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.
The AVERAGE function returns the average of a cell range.
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
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.
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.😃❤
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)
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 😉😃
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 😉
The percentile is based on the same principle as the quartile but it divide the series by group of 10%.
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.