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.

**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:

**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.

**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.

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.

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.

**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:

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:

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.

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!

[…] Image link: https://blog.luz.vc/excel/como-fazer-media-mediana-e-desvio-padrao-excel/ [...]

Cool Adriano! Success in use, hugs

Many thanks Rafael, I really needed the table, I did not do it through the site https://calculareconverter.com.br/desvio-padrao-e-variancia/, but needed something offline.

Big hug

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

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!

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?

How did you get to 2,61?

Thank you for participating Marco!

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

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!

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

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

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

Thanks Giovanna! =]

Very Good!

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.

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

Thank you Regiano!

Congratulations on the content.

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

this is trimmed

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.

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

very good explanation!

in Standard Deviation, in the calculation of the square means (68,1 / 10) would it not be 68,1 / 9 (total values - 1)?

Thank you

https://pt.wikipedia.org/wiki/Desvio_padr%C3%A3o

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

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.

Hi Emanuel,

Thanks for the comment! Let's check and change the table.

Hugs!

[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.