Statistics functions in Excel

Basic statistics functions

Analyze data is very easy with the Excel statistics functions. The most important is to understand the purpose of each one of these functions. This article will help you to understand the meaning of the basic statistical functions.

Let's work with the following rainfall records and understand how the statistical functions can help to analyse the data.

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 😉)

An alternative solution is to use the function SUM and divide by the number of cells. But it's really not convenient especially if the number of cells change ofter

=SUM(B3:B14)/12

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

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 common value in a series. In our example, the MODE is equal to 57 (2 times in our example)

=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. With this formula, we know how many time the most common value is in a series 😉😃

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

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) => 50,75

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.

Related posts


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

2 comments

    • preeti on 15/08/2019 at 12:25
    • Reply

    your sight is so useful. I am old lady and keen on learning excel to help me in my work. I did not understand quartile.

    • Emma on 18/04/2019 at 01:28
    • Reply

    What's up,I read your blog named "Basic statistics function in Excel - A lot of examples" on a regular basis.Your writing style is awesome, keep up the good work!

Leave a Reply

Your email address will not be published.