Doing average, median, and standard deviation in Excel is not as complicated as it sounds. Within the statistics in Excel there are some terms that basically define the most common measures for distributing data in a data set. In general, the most common measures are: average, fashion, median, variation, standard deviation, among others.
In this article we will cover a little about the mean, median and standard deviation as well as their respective Excel. Follow the following.
The meaning of mean in statistics refers basically to the break-even point of a given frequency or the concentration of data of a distribution.
Maria had the following notes in the Portuguese tests: 7,5; 9; 9,5 and 10. Calculate the average of the notes.
In this case, just add all the values and divide the result by the amount of values added to find the mean. See below:
Calculating Mean in Excel
Based on the above example, we will calculate the average of Maria's scores using Excel.
With the notes inserted into cells in Excel, just use the Mean formula as below. In this way we calculate the values contained in the range of (B2 to E2) that are the notes of Maria.
At the end of the formula creation, just hit "Enter" to get the result.
There is also the possibility of adding the values and divide by the amount of evidence obtaining the same result, but with the formula in Excel, such calculation becomes more simplified.
The median term refers to the measure that specifies the central tendency indicating exactly the central value of certain data.
For a better understanding, let's take a practical example.
Marcos got the following notes in the first semester: 4, 5, 7, 4, and 7. Determine the median.
Before we calculate the median of the proposal, we will organize the values in ascending order by doing the following: 4,4,5,7,7.
In this way, we can observe that the central value is the number 5 in which to be defined as median.
It is important to note that when we have an odd number of numbers, we define the median as the central number of the numbers presented as shown above. However when we have an even number of numbers, we define the median of these are defined as follows: The two central numbers are added together and the mean is calculated between them.
Calculate the median values: 1,2,3,3,5,7,8,10,10,10
In this way we have the values 5 and 7 as central.
Taking the average between them, we find the median 6.
Calculating Median in Excel
In Excel we can calculate the median of values using the MED function.
In this case, we will use the last example quoted and calculate the median of the values by Excel as below:
With the values to be calculated inserted into cells, simply apply the function = MED and include the range of cells where the values to be calculated are.
At the end of the operation just hit "Enter" and check the result.
Within the statistics, the term standard deviation aims to demonstrate the regularity of a set of data in order to indicate the degree of oscillation of these in comparison with the average of the values of the set.
Let's make use again of practical example for better understanding.
Cláudia obtained the following notes in a certain matter: 2, 3, 3, 4, 5, 6, 7, 8, 9, 10, XNUMX. Determine the standard deviation.
Step 1 - Calculate the average of the displayed values. In this way we have:
Step 2 - Calculate the deviations based on the mean obtained. Thus, we will calculate each note by subtracting each individual value by the calculated average.
Step 3- Each result obtained must be squared and then the mean squared.
Step 4 - After obtaining the average of the squares is enough to extract its square root, obtaining thus the standard deviation.
Calculating Standard Deviation in Excel
In Excel the standard deviation calculation is simplified by the STDEV formula.
Let's take the practical example for better understanding:
High school students had the following marks in Mathematics: 5, 5, 7, 8, 10, 3, 7, 8, 9, 7, XNUMX. Determine the standard deviation:
So, just insert the values in the cells of Excel, and use the formula STDEV as below, select the range containing the notes.
At the end of the procedure, just hit "Enter" and check the result.
As we can see, within the statistics we have some terms known in our daily life as well as other nomenclatures used only by professionals in the area of economics. But in parallel with such differences, Excel again emerges as a helper for calculating expressions in order to simplify calculations and always bring quick results in a practical and effective way.
And you, are you interested in statistical worksheets? Are you in doubt? Get in on LUZ Excel Forum and send your question!