How to do average, median, and standard deviation in Excel

28
76810
How to do average, median and standard deviation.
How to do average, median and standard deviation.
Excel Spreadsheets

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.

How to do average, median, and standard deviation in Excel 1

Average:

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.

Example:

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:

How to do average, median and standard deviation.

Calculating Mean in Excel

Based on the above example, we will calculate the average of Maria's scores using Excel.

How to do average, median and standard deviation.

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.

How to do average, median and standard deviation.

At the end of the formula creation, just hit "Enter" to get the result.

How to do average, median and standard deviation.

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.

Excel Online Course

Medium:

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.

Example 1:

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.

How to do average, median and standard deviation.

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.

Example 2:

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.

How to do average, median and standard deviation.

 Taking the average between them, we find the median 6.

How to do average, median and standard deviation.

How to do average, median, and standard deviation in Excel 1

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.

How to do average, median and standard deviation.

At the end of the operation just hit "Enter" and check the result.

How to do average, median and standard deviation.

Excel Online Course

Standard deviation

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.

Example:

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:

How to do average, median and standard deviation.

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.

http://i1277.photobucket.com/albums/y500/Hshopnet_Hshopnet/imagem11_zps12583265.jpg

Step 3- Each result obtained must be squared and then the mean squared.

How to do average, median and standard deviation.

Step 4 - After obtaining the average of the squares is enough to extract its square root, obtaining thus the standard deviation.

How to do average, median and standard deviation.

Excel Online Course

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:

Example:

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.

How to do average, median and standard deviation.

 At the end of the procedure, just hit "Enter" and check the result.

How to do average, median and standard deviation.

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!

How to do average, median, and standard deviation in Excel 1

Excel Spreadsheets

28 COMMENTS

  1. Hi Cláudia, I could not visualize the structure you have, to help I would need to understand a little better

  2. Good Morning! I would like to update the calculation of successive postings on different dates for a specific date. However, I do not know how to do this, because they are multiple tables. If I do this by hand it is impractical. I thought about adding the numeric value of each table and making the median of the dates, then calculating the standard deviation. Please, give me a light! Thank you!

  3. Hi Lia, what is your question exactly? The post seems to me very explanatory in the walkthrough of how to get in the 2,61 value. Did you find any miscalculation?

  4. But in the spreadsheet he used the sample standard deviation formula. He should in fact use (total value - 1), as our friend Eurico informed.

  5. Hi Diego, I believe that we put the value 2,62 in the wrong way in the first image, since when we use Excel the correct value was 2,08. It was probably a mistake during the creation of the post itself. Thank you for the warning!

  6. Good night, I'm trying to understand why in the sum of all the notes gave 69 dividing by 10 would give 6,9 so the root would give 2,62. Because excel did not give 2,08. I'm having this same problem on an account I'm trying to do

  7. Legal Maciel. If you have any positive results and would like to share here, it will be great. Hugs and any other help you need, just talk

  8. Dude, I just saw you answered today. Thank you.

    Average Trim? Cool, I did not know, excel calls it average.

    Thank you and I'm going to study.

    Abs.

    Maciel

  9. Hi Brenda, unfortunately I do not know bioEstat, so I can not help it.

    Generally speaking, the interpretation is the same, mean is a mean value of a quantity of items, the median picks up the mean value of that quantity of items and the standard deviation shows the average variation of all values ​​in relation to average of this quantity of items.

  10. Good night. Would you like to know how to interpret the mean, median and standard deviation data in bioEstat?

  11. Hi Maciel, I would use a formula similar to the one you already have, I did not think of anything much different

  12. Greetings, beautiful work. I learned a lot but I still have a question.
    Would you know if there is a formula where I average, excluding the extreme, maximum, and minimum values? Example notes:
    1) 100
    2) 8
    3) 7
    4) 7
    5) 0

    I would like to exclude the launches that I consider "out of the ordinary" and make the average, which in the example would give (8 + 7 + 7) / 3.

    I arrived at this blog looking for the standard deviation but I think that would not be it.

    PS: I have a formula where I use math but it only takes into account the maximum and the minimum, which in my case is not so useful.
    =(SUM(W1:W5)-MAX(W1:W5)-MIN(W1:W5))/(COUNT(W1:W5)-2)

    I would like to do something much more elegant.

    If someone helps I thank you very much.

  13. This formula that you are showing is the sample standard deviation, which he explained is the population.

  14. Hi James, how are you?

    If the day sale field is blank, the average function will not count it as a value. For example, if from 31 days you had 1000 releases in the first 10 days and the rest is blank, your average would be 1000 because the average only counts filled values.

    The only problem with this formula is that if 0 is written in the other 21 days, it counts and the average would be very different down giving you the wrong impression.

    What you can do is also create an auxiliary column that marks some value if the cell of the day is filled with value and after that you can use the AVERAGE function. So you'll only average if the extra column criterion is that value you've marked.

    Hugs

  15. Good afternoon, which formula should I use to calculate the average daily sale? But the formula takes 31 days of the month and I want the result to be according to the days released !! If I launch 10 days, I want the average of the 10 days and so on, I'm launching and the formula recognizing the days and recalculating. Is there such a formula? Hug.

  16. [Correcting] In standard deviation the deviation in 7 that gives the result (7 - 5,7: 0,3) is not, but 1,3 the correct result… apart from that beautiful work, I learned a lot, so much from congratulations…

Comments are closed.