Average in Excel: Formula, Examples, and Common Fixes

Formulas & functions · Updated November 21, 2026

To calculate an average in Excel, use the AVERAGE function: with numbers in B2:B10, enter =AVERAGE(B2:B10). Excel returns the arithmetic mean of the numeric values in that range, ignoring blank cells and text while including zeros.

What the AVERAGE formula calculates

An average, also called the arithmetic mean, is the sum of a set of numbers divided by how many numbers are in the set. Excel’s AVERAGE function does that calculation for you, which is useful when a list changes and you want the result to update automatically.

For example, suppose B2:B6 contains 12, 15, 18, 21, and 24. The sum is 90, and there are five numbers, so the average is 18. With those values in B2:B6, enter =AVERAGE(B2:B6); the result is 18. If you change one of the values, Excel recalculates the average.

AVERAGE can take a range, separate cell references, or a combination of numeric arguments. For a typical worksheet, selecting a continuous range is simplest. The function name is the same in Microsoft Excel for Microsoft 365 on Windows, Mac, and the web, although menus and keyboard shortcuts can differ.

How to enter an average formula

In Excel for Microsoft 365 on Windows, click the cell where you want the result, type the formula, and press Enter. You can type a range address or select the cells with your mouse while entering the formula.

  1. Choose an empty cell for the result, such as B11.
  2. Type an equals sign followed by AVERAGE and an opening parenthesis.
  3. Select the cells to include, or type their range address, such as B2:B10.
  4. Close the parenthesis and press Enter.

For numbers in B2:B10, the formula is =AVERAGE(B2:B10). The colon means “through,” so Excel includes every cell from B2 to B10. To average cells that are not next to each other, separate references with commas. With values in B2, B5, and B8, enter =AVERAGE(B2,B5,B8). The formula returns the mean of those three cells only.

You can also use the ribbon: select the cell where the result should appear, then choose Formulas > AutoSum > Average. Excel suggests a range; check that it includes the intended cells before confirming. This is convenient for a simple column, but a typed formula is often clearer when the data is separated or the suggested range is not right. Excel for Mac has the same AVERAGE function; ribbon layout can vary slightly by version.

What cells are included or ignored

Knowing what Excel counts prevents many unexpected results. When the arguments are cell references or ranges, AVERAGE includes numeric values and ignores empty cells, text, and logical values such as TRUE or FALSE. It does not treat an empty cell as zero, but a cell containing the number 0 is included. Numbers stored as text are also ignored in a referenced range until converted to numeric values.

For example, if B2:B5 contains 10, 0, 20, and a blank, the average is 10: Excel adds 10, 0, and 20, then divides by three numeric values. With those entries in B2:B5, enter =AVERAGE(B2:B5); the result is 10. If you remove the zero, the result becomes 15 because the blank still does not count.

This distinction matters in surveys, attendance records, and sales reports. A blank might mean “not recorded,” while zero might mean “recorded, but none.” Treating both the same could change the interpretation of the result. Before calculating, decide whether missing values should remain blank, be entered as zero, or be excluded using a condition.

If a referenced cell contains an error, such as #N/A or #DIV/0!, AVERAGE generally returns an error rather than silently skipping it. Find and resolve the source error, or use a formula designed to handle errors if excluding them is appropriate. Also note that values hidden by a filter are still excluded only when you use a subtotal-style function; ordinary AVERAGE includes filtered-out rows.

Average only values that meet a condition

Use AVERAGEIF to average values that match one condition, such as one region, category, or employee. Its arguments identify the cells to test, the condition, and—if different—the cells to average. Text conditions go inside straight double quotation marks.

Suppose B2:B4 contains the regions East, East, and West, while C2:C4 contains sales of 100, 140, and 200. To average sales for East, enter =AVERAGEIF(B2:B4,"East",C2:C4). The result is 120, the mean of 100 and 140. The region range and sales range need to line up row by row.

For two or more conditions, use AVERAGEIFS. For instance, with regions in B2:B5, sales in C2:C5, and a status in D2:D5, you can average sales where the region is East and the status is Complete. If the rows are East/100/Complete, East/140/Pending, West/200/Complete, and East/160/Complete, enter =AVERAGEIFS(C2:C5,B2:B5,"East",D2:D5,"Complete"). The result is 130, the average of 100 and 160. AVERAGEIFS puts the range to average first, followed by each criteria range and its condition.

Criteria can also use comparison operators. For example, the condition greater than 100 is written as a text criterion. If a criterion depends on a value in another cell, join the operator to that cell reference with an ampersand. This lets a report change when the threshold changes, rather than requiring you to edit the formula each time.

Average visible or filtered rows

AVERAGE calculates across every numeric cell in its range, even if some rows are hidden by a filter. To average only rows currently visible after filtering, use SUBTOTAL with function number 1. With values in B2:B20, enter =SUBTOTAL(1,B2:B20). The result is the average of visible values, and rows removed by a filter are left out.

There is a subtle difference between filtered and manually hidden rows. SUBTOTAL with function number 1 ignores filtered-out rows but includes manually hidden rows. If manually hidden rows should be excluded too, use function number 101. With the same B2:B20 range, enter =SUBTOTAL(101,B2:B20). Check which rows are hidden and how they were hidden before choosing between the two. SUBTOTAL is useful for a filtered list, but a normal AVERAGE is more appropriate when every row should count regardless of display.

AVERAGE compared with related functions

AVERAGE is not the only way to summarize a set of numbers. Choose the function that matches the question your data is meant to answer:

  • AVERAGE: Finds the arithmetic mean of numeric values. It is sensitive to unusually high or low numbers.
  • AVERAGEIF and AVERAGEIFS: Find the mean only for values meeting one or several conditions.
  • MEDIAN: Finds the middle value after sorting. It can better represent a typical value when a few outliers skew the mean.
  • MODE.SNGL: Finds the most frequently occurring value when the most common result is what matters.
  • SUBTOTAL: Can calculate an average that responds to filtered or hidden rows.

For example, a small group of salaries with one exceptionally high value may have a mean well above what most people earn. The median answers a different question—the middle observation—so the choice is about what “average” should communicate, not which formula is universally better. In everyday use, people often say “average” to mean the mean, but it is worth stating which measure a report uses.

Weighted averages and common pitfalls

A simple AVERAGE gives each numeric entry equal weight. That is not always appropriate. If one product sold 2 units at a price of 10 and another sold 8 units at a price of 20, averaging the two prices directly gives 15, but the average price paid across all 10 units is 18. The latter is a weighted average because each price is weighted by its quantity.

With prices in B2:B3 and quantities in C2:C3, enter =SUMPRODUCT(B2:B3,C2:C3)/SUM(C2:C3). The result is 18: (10 times 2 plus 20 times 8) divided by 10 total units. Check that the weights add to a nonzero total; if they sum to zero, the division produces an error. This method also applies to weighted scores or rates when the weights represent the relevant counts or proportions.

Other common problems are straightforward to diagnose:

  • The result is #DIV/0!: The range contains no numeric values. Check for numbers stored as text, an incorrect range, or cells that are all blank.
  • The average seems too low: Look for zeros, which count, and check whether the range includes extra rows or values that should not be part of the calculation.
  • The average seems too high: Check for outliers, duplicate entries, or a range that omits some low values.
  • A result is wrong after filtering: AVERAGE still includes filtered-out rows. Use SUBTOTAL if the result should reflect visible rows only.
  • The formula displays as text: Confirm the cell is not formatted as Text, then re-enter the formula. Also check that it begins with an equals sign.

Excel normally uses commas between function arguments in the examples here. Depending on regional settings, a copy of Excel may require semicolons instead. If a valid formula is rejected, check the list separator configured for your installation; do not change the function name or range logic.

Frequently Asked Questions

What is the formula for average in Excel?
Use AVERAGE with the cells to calculate, such as =AVERAGE(B2:B10) when the values are in B2:B10. Excel returns the arithmetic mean of numeric entries in that range.

Does AVERAGE count blank cells as zero?
No. Blank cells in a referenced range are ignored. A cell containing the numeric value 0 is counted, so entering zero instead of leaving a cell blank can lower the result.

How do I average a column while ignoring errors?
First consider whether the errors indicate data that should be fixed. AVERAGE does not automatically skip errors in its range. Wrapping the calculation in IFERROR can display a chosen message if the result errors, but it does not selectively remove error cells from the calculation.

How do I average only numbers above a threshold?
Use AVERAGEIF, with the range to test, the condition, and the range to average if needed. For example, a criterion of greater than 100 can restrict the average to values above that threshold. Make sure the test range and values to average correspond to the same rows.

Why is my Excel average different from the calculator result?
Check which entries were included in each calculation. AVERAGE ignores blanks and text, includes zeros, and may include rows hidden by a filter. Also verify that the worksheet range does not include a header, total, or unrelated value.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.