↑ Return to Statistic

AVERAGE – QUARTILE – MEDIAN

With calculation functions of Excel, it will be very easy to perform statistical calculations. Through an exercise, we will see how to use statistical functions with a single variable.

Let’s work with the following rainfall records

AVERAGE

The AVERAGE function returns the average of a cell range.

=AVERAGE(B3:B14)

The average rainfall is 101,83

 

 

 

 

 

 

 

 

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 quantity of rainfall the most probable in the year is 100.

 

 

 

 

 

 

 

MODE

The mode is the number most often presented in a series.

In our example, the mode is equal to 57.

 

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

=MODE(B3:B14)

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, one can obtain the number of times what counted the most common in a series.

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

 

 

 

 

 

 

 

 

 

 

 

QUARTILE

A quartile is one of three points that divide a data set into four equal groups, each representing a fourth of the distributed sampled population.

In our example, we will split the values ​​into 4 groups ranging 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.

We note that the QUARTILE 2 is equivalent to the median

 

 

 

 

 

 

 

 

PERCENTILE

The percentile is based on the same principle as the quartile but in refining the accuracy with 10%.

 

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

This is the main difference with the QUARTILE function.

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

Leave a Reply

%d bloggers like this: