EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tips How to Find Mean in Excel
 

How to Find Mean in Excel

Steffi Madari
Article bySteffi Madari
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated July 12, 2023

Find Mean in Excel

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

  1. Example #1: AVERAGE
  2. Example #2: Finding the Mean of a Data Set
  3. Example #3: AVERAGEIF
  4. Example #4: AVERAGEIFS
  5. Example #5: AVERAGEA

Syntax or Formula of Mean
The syntax for calculating the mean is as follows:
Syntax or Formula of Mean

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:

  1. Select an empty cell to display the result
  2. Enter the formula =AVERAGE(cell range). For instance: =AVERAGE(A5:A20)
  3. Press “Enter”
  4. The formula returns the arithmetic mean.
You can download this How to Find Mean Excel Template here – How to Find Mean Excel Template

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.
Example 1

Solution:
Step 1: Select “Cell C2” and enter the formula:

=AVERAGE(A2:A11)

Example 1 Step 1

Note: In the formula =AVERAGE(A2:A11), we have selected the range from A2 to A11. You can also type individual numbers in the formula.

Step 2: Press “Enter”.

Result: The formula will return 27.5.

Example 1 Step 2

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”.
Example 1 Alternate Method Step 2

When you click on Average, the function automatically selects the data, and the formula will appear in Cell A12, as shown below.
Example 1 Alternate Method Step 2-2

Step 3: Press “Enter”

Result: The formula will display 27.5 in Cell A12.

Example 1 Alternate Method Step 3

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.
How to Find Mean in Excel-Example 2

Solution:
Step 1: Insert a Column “Average”, as shown below.
Example 2 Step 1

Step 2: Select “Cell F2” and enter the formula:

=AVERAGE(B2:D2)

How to Find Mean in Excel-Example 2 Step 2

Step 3: Press “Enter”.
The formula will return 71 in Cell F2.
Example 2 Step 3

Step 4: Drag the cell downwards.

Result: The formula will return average marks for respective students, as shown below.

How to Find Mean in Excel-Example 2 Step 4

Note: To remove the decimal number or round off the result,  you can use the below formula
=ROUND(AVERAGE(B3:D3),0)

The formula round off the decimal number into two digits numbers, as shown below.
Example 2 Step 4-2

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.
How to Find Mean in Excel-Example 3

Solution:
Let’s first calculate the average of the given data.
Step 1: Select “Cell C11” and enter the formula:

=AVERAGE(C2:C10)

Example 3 Step 1

Step 2: Press “Enter”.
The formula will display the average of the total sales.
How to Find Mean in Excel-Example 3 Step 2

Now, we will calculate the total sales of sugar using the AVERAGEIF function.
Step 3: Select “Cell C12” and enter the below formula:

=AVERAGEIF(A2:A10,“Sugar”,C2:C10)

Example 3 Step 3

Step 4: Press “Enter”.

Result: The formula return the average of total sales for only sugar.

How to Find Mean in Excel-Example 3 Step 4

Note: You can see the difference between the AVERAGE and AVERAGEIF function results. The AVERAGE function has considered values from C2 to C10. The AVERAGEIF function has considered only Cell C4, C6, C8, and C9 values, representing sugar.

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:

=AVERAGEIFS(C2:C10,A2:A10,”Sugar”,C2:C10,”>40″)

Example 4 Step 1

Step 2: Press “Enter”.

Result: The formula returns the average value of total sugar sales exceeding $40.

How to Find Mean in Excel-Example 4 Step 2

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.
Example 5

Solution:
Step 1: Select “Cell C12” and enter the formula:

=AVERAGEA(C2:C10)

How to Find Mean in Excel-Example 5 Step 1

Step 2: Press “Enter”.
The formula will return 25.47777778 in Cell C12, as shown below.

Result: The formula will return the average of total sales

Example 5 Step 2

To round the average value, use the “Decrease Decimal” command in the “Number” group of the “Home” tab, as shown below.
How to Find Mean in Excel-Example 5 Step 2-2

Let’s calculate the average of total sales using the AVERAGE function.
Step 3: Select “Cell C13” and enter the formula:

=AVERAGE(C2:C10)

Example 5 Step 3

Step 4: Press “Enter”.
The formula will display 32.76 in Cell C13.
Example 5 Step 4

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.
How to Find Mean in Excel-Example 5 Step 5

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.

  1. Scrollbar in Excel
  2. MATCH in Excel
  3. CheckBox in Excel
  4. VLOOKUP in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download How to Find Mean Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download How to Find Mean Excel Template

EDUCBA

डाउनलोड How to Find Mean Excel Template

🚀 Limited Time Offer! - ENROLL NOW