«

»

Statistics functions in Excel

Basic statistics functions

Analyze data is very easy with the Excel functions. If you are not a genius in maths, the Excel functions will do the job for you 😋.

The most important is to understand the purpose of each one of these functions. This post will help you to understand the meaning of the basic statistical functions.

Let's work with the following rainfall records and the analyze we can do.

AVERAGE

The AVERAGE function returns the average of a cell range.

=AVERAGE(B3:B14)

The average is easy to understand. You make the sum of your data, and you divide by the number of values (a kid a 10 years old now that 😉)

 

 

MEDIAN

The median is a number that divides the sample into two parts. The population or the probability distribution such that each part contains the same number of values.

=MEDIAN(B3:B14)
So in this example, the most probable quantity of rainfall  in the year is 100.

 

 

 

Difference between Median and Average

Let's say you have data and you sort them from the lowest to the greatest 🤔

3 - 8 - 10 - 11 - 13. The median is 10 (the middle of your series) and the average is 9 (yes, the first value has an impact on the global result for the average).

Now if you have a series with a even number of data, it's slightly different.

3 - 8 - 10 - 11 - 12 - 13. The median is 10,5 (the gap between the 2 values of the middle of the series 10 and 11). The average in that case is 9.5.

So, the median doesn't consider the "extreme" values but the middle values in your list of data.😃❤

MODE

The mode function returns the number of the most often presented in a series.

In our example, the mode is equal to 57

=MODE(B3:B14)

If all values ​​are unique, the function returns an error.

Be careful to not confuse the MODE function with the function MOD (modulo)

 

 

 

It is interesting to combine the MODE function with the COUNTIF function in a single formula.

=COUNTIF(B3:B14;MODE(B3:B14))

With this function, we know how many time the most common value is in a series 😉😃

 

 

QUARTILE

A quartile divide a series in 4 equal groups, each representing a fourth of the population.

In our example, we will split the values ​​into 4 groups from 15 to 186. The function needs the range of cells that contains data and a value corresponding to the quartile

The last parameter range from 1 (first quarter) to 4 (last quarter).

The first quartile (25% of the rainfall) is:

=QUARTILE(B3:B14,1)

 

The following table shows the breakdown by quartile.

Note : the 2nd quartile = the median 😉

 

 

 

 

PERCENTILE

The percentile is based on the same principle as the quartile but it divide the series by group of 10%.

=PERCENTILE($B$3:$B$14,D3)

 

 

 

This is the main difference with the QUARTILE function.

  • The QUARTILE function expects a value between 1 and 4
  • The PERCENTILE function expects a percentage.

 

Permanent link to this article: https://www.excel-exercise.com/statistics-functions-excel/


Leave a Reply

Your email address will not be published. Required fields are marked *