Excel offers many statistical functions that are easy to use because Excel does the calculation for you.

The most important is to understand the result returned by these functions. Here is the list of the most useful statistics functions of Excel.

Analyze the wages of your employees

Let's take this document. We will start with the following table, which contains the salaries of employees:

And the distribution of wages is as follows:

Basic statistical functions

Some of these functions are undoubtedly known, but others will be new.

Min and Max functions

These functions return the minimum and maximum value of our data series:

=MIN(tbl_Employee[Salary]) => $ 1,820

=MAX(tbl_Employee[Salary]) => $ 6,000

How many values are in our series?

Two functions can be used here

=COUNT(tbl_Employee[Salary]) => 39

=COUNTA(tbl_Employee[Salary]) => 39

The 2 functions return the same value here because each cell contains a numeric value. The COUNT function counts only the numeric values in the range of cells. The COUNTA function counts ALL values (texts and numbers) in the cells.

Analysis functions of the entire series

Here we will see several statistical functions that return various results. Properly used, these functions will reveal important information about your data.

The AVERAGE function

Everyone knows the principle of averaging; the sum of the values is divided by the number of values.

On the other hand, what many people forget is that the average gives only a partial vision. Indeed, it is always necessary to complete the mean by the standard deviation.

=AVERAGE(tbl_Employee[Salary]) => $ 2,523.85

Standard deviation

I have already presented my interest in the standard deviation in this article. But to summarize, it allows you to find the dispersion of your values around the average.

=STDEV.P(tbl_Employee[Salary]) => $ 923.13

The difference between mean and standard deviation is essential information. Without going into too much detail, 68% of the data is between the mean and 1 times the standard deviation. Twice the standard deviation, you aggregate 95% of your data.

The Median

This function isn't popular, but it returns very important information. The median returns the value that divides our series into two equal parts.

=MEDIAN(tbl_Employee[Salary]) => $ 2,200

In our situation, we can therefore say that half of the salaries are less than $ 2,200 (and also the opposite).

You can also see that the results of an Average and a Median have nothing to do with each other and should not be confused.

The Mode

The MODE.SNGL function (do not confuse with the MOD function) returns the most commonly present value:

=MODE. SNGL(tbl_Employee[Salary]) => $ 1,870

So this indicates that the most common salary is $ 1,870 in our company.

And you can combine this function with COUNTIFS to know when this result is in the list of values.

=COUNTIFS( tbl_Employee[Salary]; MODE(tbl_Employee[Salary]) => 4

The salary $ 1,870 is present 4 times in our list 😉