TOP 10 Most Useful Statistical Functions for Data Analysis

TOP 10 Most Useful Statistical Functions for Data Analysis
Last Updated on 12/05/2024
Reading time: 3 minutes

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 MOD

Analyze the wages of your employees

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

Table of salary and the list of the statistical functions

And the distribution of wages is as follows:

Distribution chart

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

Result MIN and MAX function.

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.

Result of the COUNT function

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

Result of the AVERAGE function, one of the most statical function used

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

Result of the Standard Deviation function

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

Result of the Median function

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

Table with all the most common statistical functions

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 😉

Related Article

Leave a Reply

Your email address will not be published. Required fields are marked *

TOP 10 Most Useful Statistical Functions for Data Analysis

Reading time: 3 minutes
Last Updated on 12/05/2024

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 MOD

Analyze the wages of your employees

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

Table of salary and the list of the statistical functions

And the distribution of wages is as follows:

Distribution chart

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

Result MIN and MAX function.

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.

Result of the COUNT function

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

Result of the AVERAGE function, one of the most statical function used

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

Result of the Standard Deviation function

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

Result of the Median function

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

Table with all the most common statistical functions

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 😉

Related Article

Leave a Reply

Your email address will not be published. Required fields are marked *