Updated July 12, 2023
What is Mean in Excel?
In Excel, the mean is also called the average. It’s a function that calculates the arithmetic mean, which means finding the average of a set of numbers. The average function adds all the values of a given range and divides the sum by the total count of cells.
In this article, we will learn How to Find Mean in Excel. For example, if you want to calculate the mean for 5, 10, 15, 20. The average function will add all the values (5 +10+15+20) and divide the sum by the total number of cells, i.e., 4. The result will be 12.5.
The AVERAGE function is useful to calculate the average marks of students, the average salary of employees, and the average sales produced over time. This article explains how to find mean in Excel with five different methods and examples.
- Example #1: AVERAGE
- Example #2: Finding the Mean of a Data Set
- Example #3: AVERAGEIF
- Example #4: AVERAGEIFS
- Example #5: AVERAGEA
Syntax or Formula of Mean
The syntax for calculating the mean is as follows:
Where,
- number1-It is a mandatory argument. It can be a number, range, or cell reference
- [number2],..-It is an optional argument in the function.
How to Find Mean in Excel?
To find the mean in Excel, follow the below steps:
- Select an empty cell to display the result
- Enter the formula =AVERAGE(cell range). For instance: =AVERAGE(A5:A20)
- Press “Enter”
- The formula returns the arithmetic mean.
Example #1: How to Calculate Arithmetic Mean in Excel?
Consider the following data of some random numbers, and you want to calculate the arithmetic mean.
Solution:
Step 1: Select “Cell C2” and enter the formula:
Step 2: Press “Enter”.
Alternate Method:
Excel has an in-built function to calculate the average values in a continuous row or column. You can follow the following steps.
Step 1: Click the cell below the data, i.e., “Cell A2”
Step 2: Go to the “Home” tab, click the drop-down arrow next to AutoSum in the “Editing” group, and select “Average”.
When you click on Average, the function automatically selects the data, and the formula will appear in Cell A12, as shown below.
Step 3: Press “Enter”
Example #2: How to Find the Mean of a Data Set?
Let’s consider the below data of student marks in Math, English, and Science out of 100. You have to calculate the average marks for individual students.
Solution:
Step 1: Insert a Column “Average”, as shown below.
Step 2: Select “Cell F2” and enter the formula:
Step 3: Press “Enter”.
The formula will return 71 in Cell F2.
Step 4: Drag the cell downwards.
=ROUND(AVERAGE(B3:D3),0)
The formula round off the decimal number into two digits numbers, as shown below.
Example #3: Using AVERAGEIF for a Specific Condition
The AVERAGEIF function calculates the mean or average of a given data that meet certain criteria.
Consider the below sales data for rice, wheat, and sugar in different zones (P, C, H, and A). You need to calculate the average total sales of only sugar products.
Solution:
Let’s first calculate the average of the given data.
Step 1: Select “Cell C11” and enter the formula:
Step 2: Press “Enter”.
The formula will display the average of the total sales.
Now, we will calculate the total sales of sugar using the AVERAGEIF function.
Step 3: Select “Cell C12” and enter the below formula:
Step 4: Press “Enter”.
Example #4: Using AVERAGEIFS for Multiple Conditions
In the earlier example, we calculated the average of total sugar sales. Let’s say you want to calculate the average total sugar sales exceeding $40. You have to use the AVERAGEIFS function to accomplish this task.
Solution:
Step 1: Select “Cell C11” and enter the formula:
Step 2: Press “Enter”.
Example #5: Using AVERAGEA
In contrast to AVERAGE, the AVERAGEA function will also evaluate logical values (Boolean values) like TRUE and FALSE, numbers, and text strings.
Points to remember while using the AVERAGEA function:
- Text strings are evaluated as
- Boolean or Logical value like TRUE is considered 1 and FALSE as 0.
- Empty text (“”) in the cell is also evaluated as 0.
Consider the following data. Here, Cell C5 and Cell C10 contain text instead of numbers. You have to calculate the average of total sales.
Solution:
Step 1: Select “Cell C12” and enter the formula:
Step 2: Press “Enter”.
The formula will return 25.47777778 in Cell C12, as shown below.
To round the average value, use the “Decrease Decimal” command in the “Number” group of the “Home” tab, as shown below.
Let’s calculate the average of total sales using the AVERAGE function.
Step 3: Select “Cell C13” and enter the formula:
Step 4: Press “Enter”.
The formula will display 32.76 in Cell C13.
As you can see, the AVERAGEA and AVERAGE functions have given different results.
The calculation of the AVERAGEA function is:
24.38+21.65+36.12+0+40.25+19.56+45.32+42.02+0/9 = 25.48,
Here, the AVERAGEA function has taken the Cell C5 and Cell C10 values (Text-Non Sold) as 0 and divided the sum by 9, i.e., the total count of cells in the data.
Whereas the calculation of the AVERAGE function is:
24.38+21.65+36.12+40.25+19.56+45.32+42.02/7=32.76,
Here, the AVERAGE function ignores Cell C5 and Cell C10 values and divides the sum by 7.
Let’s consider another scenario where instead of a text string, the value of Cell C5 and Cell C10 is 0.
There will be no change in the AVERAGEA value. However, as shown below, the AVERAGE function value changed from 32.76 to 25.48 as the AVERAGE function considers 0 as a value.
Things to Remember on How to Find Mean in Excel
- A user can give 255 input arguments in the AVERAGE function, which includes numbers, text strings, arrays, cell references, and logical values (TRUE or FALSE).
- If any cells do not meet the above criteria, Excel will display a #DIV01 error.
- The AVERAGEA function will evaluate 1 for TRUE and 0 for FALSE values.
- To calculate the mean with specific conditions, use AVERAGEIF or AVERAGEIFS.
Recommended Articles
This article provides a step-by-step tutorial on how to find mean in Excel using various functions like AVERAGE, AVEARGEIF, AVEARGEIFS, and AVERAGEA with practical examples. To understand more about Excel features, you can also read our other articles.
