The tutorial will teach you how to find an average in Excel with or without formulas and round the results to as many decimal places as you want.
In Microsoft Excel there are a handful of different functions for calculating the average for a set of numeric values. Moreover, there is an instant non-formula way. On this page, you will find a quick overview of all the methods illustrated with examples of use and best practices. All the functions discussed in this tutorial work in any version of Excel 365 through Excel 2007.
In everyday life, the average is a number expressing the typical value in a dataset of data. For example, if a few athletes have run a 100m sprint, you may want to know the average result – i.e. how much time most sprinters are expected to take to complete the race.
In mathematics, the average is the middle or central value in a set of numbers, which is calculated by dividing the sum of all the values by their number.
In the above example, assuming the first athlete covered the distance in 10.5 seconds, the second needed 10.7 seconds, and the third took 11.2 seconds, the average time would be 10.8 seconds:
Calculating averages in Excel is a common task that allows you to understand the central tendency of your data. There are a few different types of averages that you can calculate including the arithmetic mean median, mode, and weighted average. Excel provides functions for all of these, making it easy to find the average you need.
In this comprehensive guide, I’ll walk you through the step-by-step process for calculating various types of averages in Excel using the built-in AVERAGE, MEDIAN, MODE, and weighted average formulas. I’ll also provide examples so you can see how each formula works. Whether you’re an Excel beginner or an expert, this guide will teach you everything you need to know to find averages in Excel like a pro!
Overview of Average Functions in Excel
Before we dive into the specifics, let’s briefly overview the main average functions in Excel
-
AVERAGE – Calculates the arithmetic mean or simple average of a dataset. Ignores text and logical values.
-
AVERAGEA – Calculates the arithmetic mean of a dataset, including text and logical values
-
MEDIAN – Finds the middle value in a dataset. Half the values are above and half are below.
-
MODE – Identifies the most frequently occurring value in a dataset.
-
Weighted Average – Calculates an average where some values contribute more “weight” than others.
Now let’s look at how to use each of these functions with examples.
Using the AVERAGE Function
The AVERAGE function is useful when you want to calculate the arithmetic mean or simple average of a set of numerical data. Here is the syntax:
=AVERAGE(number1, number2, ...)To show you how it works, let’s calculate the average exam score for a group of 7 students:
| Student | Exam Score |
|---|---|
| A | 15 |
| B | 12 |
| C | 0 |
| D | 18 |
| E | 11 |
| F | |
| G | 13 |
To find the average, we’ll use the following formula, where B2:B8 is the range containing the scores:
=AVERAGE(B2:B8)This returns an average of 11.
A couple things to note about AVERAGE:
-
It ignores text values, logical values, and empty cells. This is why Student F’s empty score is excluded.
-
It includes the value of 0 for Student C’s score.
-
It will return an error if any cell contains an error value.
Using AVERAGE is a quick and easy way to find the arithmetic mean of purely numerical data in Excel.
Using the AVERAGEA Function
The AVERAGEA function is similar to AVERAGE, but with a key difference: it includes text and logical values in the calculation. The syntax is:
=AVERAGEA(value1, value2, ...)Let’s reuse the example above, this time using AVERAGEA:
=AVERAGEA(B2:B8)This returns an average of 10.
With AVERAGEA:
-
Empty cells like Student F are counted as 0.
-
Logical values like TRUE count as 1, FALSE as 0.
-
Text values are counted as 0.
So AVERAGEA is useful when your data contains a mix of numbers, text, and logical values.
Finding the Median with MEDIAN
The MEDIAN function finds the middle value from a dataset – the point where half the numbers are above it and half are below. The syntax is:
=MEDIAN(number1, number2, ...)To find the median of our students’ exam scores, we use:
=MEDIAN(B2:B8)This returns a median value of 12, which represents the middle point of the scores.
Key points about MEDIAN:
-
It works like AVERAGE – ignores text, logical values, and errors.
-
The median may or may not be an actual value in your dataset.
-
For even datasets, it finds the average of the two middle values.
MEDIAN is useful when you want to find the true middle value rather than the average, which can be skewed by outliers.
Finding the Mode with MODE
The MODE function returns the most frequently occurring value in a dataset. Here is the syntax:
=MODE(number1, number2, ...)To find the mode of the exam scores, we use:
=MODE(B2:B8)This returns a mode of 13, which is the most common score in the dataset.
Things to know about MODE:
-
Like other average functions, ignores text and logical values.
-
If there are multiple values with the same frequency, it will return the smallest.
-
If there is no most frequently occurring value, it will return #N/A.
The mode is helpful in understanding the most common or typical value in a dataset.
Calculating Weighted Averages
A weighted average allows you to calculate an average where some values “weigh” more heavily than others. The general formula is:
=SUM(weight1*value1, weight2*value2, ...) / SUM(weight1, weight2, ...)As an example, let’s say we want to calculate the average price of the items in this table, giving more weight to units sold:
| Item | Price | Units Sold |
|---|---|---|
| Shirt | $10 | 100 |
| Pants | $15 | 150 |
| Socks | $5 | 200 |
Here is the weighted average formula:
=SUM(B2*C2,B3*C3,B4*C4)/SUM(C2,C3,C4)This returns a weighted average price of $9.17. The prices are multiplied by units sold to give them appropriate weights before summing and dividing.
Key things about weighted averages:
-
Allows you to account for different levels of importance.
-
Can use any number or cell reference for the weights.
-
Watch out for dividing by zero errors.
Weighted averages are extremely flexible and allow you to calculate a specialized average tailored to your particular dataset.
AVERAGEIF and AVERAGEIFS formulas – usage notes
Excel AVERAGEIF and AVERAGEIFS functions have much in common, in particular which values they calculate and which ignore:
- In the average range, empty cells, text values, logical values TRUE/FALSE are ignored.
- In criteria, empty cells are treated as zero values.
- The wildcard characters such as question mark (?) and asterisk (*) can be used in criteria for partial match.
- If no cell meets all of the specified criteria, a #DIV0! error occurs.
AVERAGEIFS function – average with multiple criteria
To do average with two or more conditions, use the plural counterpart of AVERAGEIF – the AVERAGEIFS function. AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
The function has the following syntax:
- Average_range (required) – the range to average.
- Criteria_range (required) – the range to be tested against criteria.
- Criteria (required) – the condition that determines which cells to average. It can be supplied in the form of a number, logical expression, text value, or cell reference.
1 to 127 criteria_range / criteria pairs can be supplied. The first pair is required, subsequent ones are optional.
In essence, you use AVERAGEIFS similarly to AVERAGEIF, except that more than one condition can be tested within a single formula.
Supposing some students did not take tests in certain subjects and have zero scores. You aim to find an average score in a specific subject ignoring zeros.
To accomplish the task, you build an AVERAGEIFS formula with two criteria:
- Define the range to average (C3:C15).
- Specify the range to check against the 1st condition (B3:B15 – items).
- Express the 1st condition (“math” or F3 – the target item enclosed in quotation marks or reference to the cell containing the item).
- Specify the range to check against the 2nd condition (C3:C15 – scores).
- Express the 2nd condition (“>0” – greater than zero).
By assembling the above components together, we get the following formula:
=AVERAGEIFS(C3:C15, B3:B15, "math", C3:C15, ">0")
=AVERAGEIFS(C3:C15, B3:B15, F3, C3:C15, ">0")
The below makes it clear that only two cells (C6 and C10) meet both conditions, and therefore only these cells are averaged. 
For more information, check out Excel AVERAGEIFS function.
How To Calculate The Average In Excel
How to find average in Excel?
Step 1: Select Insert → Function → AVERAGE. Step 2: Just click and drag the cells to find the average. Here, we have selected the cells from A1 to A7 inorder to find the average of those numbers. Step 3: Hit “Enter” button to get the result. Option 2: Step 2: Press “Enter” to display the result.
How do I calculate the average of values in cells B2 B3 B4 & B5?
To calculate the average of values in cells B2, B3, B4, and B5 enter: This can be typed directly into the cell or formula bar, or selected on the worksheet by selecting the first cell in the range, and dragging the mouse to the last cell in the range.
How do you calculate average arithmetic mean?
Observe the result in the cell you entered the formula in. The average, or arithmetic mean, is determined by finding the sum of the numbers in the cell range (80) and then dividing the sum by how many numbers make up the range (10), or 80 / 10 = 8. If you calculated the sum as suggested, you can verify this by entering “=A11/10” in any empty cell.
