Excel offers many statistical functions that are easy to use. This article will explain to you what is
- Min and Max
- The Average, The Standard Deviation
- The MEDIAN and the MODE
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 in Excel
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 function
The MODE.SNGL function (do not confuse with the Modulo function MOD) 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. 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 😉
