# TOP 10 Most Useful Statistics Excel Functions for Data Analysis

Last Updated on 12/11/2023

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:

And the distribution of wages is as follows:

## Basic statistical functions

### 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 😉

# TOP 10 Most Useful Statistics Excel Functions for Data Analysis

Last Updated on 12/11/2023

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:

And the distribution of wages is as follows:

## Basic statistical functions

### 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 😉