Average Equation in Excel: Formulas, Uses, and Examples

Formulas & functions · Updated November 21, 2026

The average equation in Excel is =AVERAGE(range). For example, with numbers in B2:B6, enter =AVERAGE(B2:B6) in another cell to calculate their arithmetic mean; Excel adds the numeric values and divides by how many numbers there are.

What “average” means in Excel

In everyday use, “average” usually means the arithmetic mean: add a set of numbers, then divide the total by the number of values. Excel’s AVERAGE function applies that definition to selected cells or values. If the numbers are 12, 18, and 30, their total is 60 and their average is 20.

“Average” can also refer to other measures of a typical value, especially the median and mode. These are different calculations. The median is the middle value after sorting the data, while the mode is the value that occurs most often. AVERAGE does not calculate either one. Choosing the right measure matters when data includes unusually large or small values, such as a few very high salaries among many lower ones.

Excel’s function name is AVERAGE, rather than a special equation symbol. You can type the formula directly into a cell or use the function tools on the ribbon. The result updates when values in the referenced range change, provided calculation is set to automatic, as it normally is in Excel.

How to enter the AVERAGE formula

In Excel for Microsoft 365 on Windows, select the cell where the result should appear, type an equals sign followed by the function and range, and press Enter. For a simple vertical list in B2:B6, enter =AVERAGE(B2:B6). Excel calculates the result in the selected cell. The colon means “through,” so B2:B6 includes B2, B3, B4, B5, and B6.

You can also select the result cell and choose Home > AutoSum > Average, then confirm or adjust the range Excel selects and press Enter. AutoSum is convenient, but check the highlighted range before accepting it: Excel may select adjacent cells that are not part of the intended data. You can include multiple ranges by listing them as separate arguments, with commas between them.

For a quick calculation without keeping a formula in the worksheet, select the numeric cells and look at the status bar at the bottom of the Excel window. If Average is not shown, right-click the status bar and select Average. This display is a convenience, not a cell formula, so it is not suitable when you need the result to appear in a report or feed another calculation.

A worked example with blanks and zeros

Suppose B2:B6 contains 10, 20, a blank cell, 30, and 40. With those values in B2:B6, enter =AVERAGE(B2:B6). Excel ignores the blank cell, adds 10 + 20 + 30 + 40, and divides 100 by four, giving 25.

A zero is not the same as a blank. If B2:B6 instead contains 10, 20, 0, 30, and 40, the formula =AVERAGE(B2:B6) returns 20: the total is 100 and there are five numeric values. This distinction often explains why a result differs from someone’s manual calculation. A zero may be a real measurement, while a blank may mean that no measurement was recorded.

If zero values should be excluded for a specific analysis, do so deliberately rather than deleting them. With the same numbers in B2:B6, use =AVERAGEIF(B2:B6,">0"). The condition keeps values greater than zero, so the result is 25. This is appropriate only when zero truly means “exclude this observation”; if zero is a valid result, removing it changes the meaning of the average.

Average a range, selected values, or filtered data

AVERAGE accepts a range, individual numeric values, or several ranges. For example, if the first group of measurements is in B2:B5 and a second group is in D2:D5, enter =AVERAGE(B2:B5,D2:D5) in the result cell to average all numeric entries in both groups together. This is useful when data is separated by layout, although keeping related observations in one continuous column can make later analysis easier.

AVERAGE includes values in hidden rows and rows removed from view by a filter. If you want a result that responds to filtering, use SUBTOTAL instead. With values in B2:B20, enter =SUBTOTAL(1,B2:B20) to calculate an average that excludes rows hidden by a filter. The function number 1 specifies average. This version includes rows hidden manually; use function number 101 when manually hidden rows should also be ignored: =SUBTOTAL(101,B2:B20). Check the intended behavior before sharing a filtered summary, since the two options can give different results.

Average values that meet a condition

AVERAGEIF calculates the mean for values that meet one condition. A common setup has a category in column A, a measurement in column B, and the category to match in D2. With categories in A2:A10, measurements in B2:B10, and the desired category in D2, enter =AVERAGEIF(A2:A10,D2,B2:B10). Excel averages only the measurements whose corresponding category matches D2. For example, if D2 contains North, the formula returns the average measurement for North rows.

AVERAGEIFS handles more than one condition. If A2:A20 contains regions, B2:B20 contains product names, C2:C20 contains sales amounts, and E2 and F2 hold the region and product to match, use =AVERAGEIFS(C2:C20,A2:A20,E2,B2:B20,F2). The first range is the values to average; each following pair is a condition range and its criterion. All ranges should have the same dimensions so each row’s conditions line up with its value.

If no entries meet the conditions, Excel returns a division-by-zero error rather than an average. You can handle that case with IFERROR. With the same category and measurement layout above, enter =IFERROR(AVERAGEIF(A2:A10,D2,B2:B10),"No matching data"). This displays a message when there is no match. Use a clear message that distinguishes “no matching records” from a genuine numeric average of zero.

Weighted average: when a simple mean is not enough

A simple average gives every value equal influence. A weighted average gives some values more influence, based on weights such as credit hours, quantities, or percentages. For example, suppose scores 80, 90, and 70 are in A2:A4, with weights 20%, 30%, and 50% in B2:B4. Enter =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4). The weighted total is 80×0.20 + 90×0.30 + 70×0.50 = 78, and the weights sum to 1, so the result is 78.

If the weights are counts such as 2, 3, and 5 instead of percentages, the same formula returns 78 because the weighted total is 780 and the total weight is 10. A common mistake is to average the scores directly, which gives 80 and treats all three scores as equally important. Another is to use weights that do not represent the intended proportions. Confirm that the values and weights refer to corresponding rows and that weights are appropriate for the question being asked.

Common problems and how to check them

When an average seems wrong, first verify the range. A header row, an old value below the list, or a missing final row can change the result. Then check how Excel is treating each cell:

  • Blank cells: AVERAGE ignores empty cells in a referenced range.
  • Zeros: AVERAGE includes numeric zeros in both the sum and the count.
  • Text: Text within a referenced range is not counted as a number. A number stored as text can therefore be skipped.
  • Errors: An error value in the referenced range generally causes AVERAGE to return an error. Find and correct the source, or decide explicitly how errors should be handled.
  • Unequal ranges: In AVERAGEIF or AVERAGEIFS, mismatched range sizes can produce errors or unintended row matching. Keep condition ranges aligned with the values being averaged.

To spot numbers stored as text, look for warning indicators or inconsistent cell alignment, then use Excel’s conversion options or re-enter the values as numbers. Avoid fixing a suspicious result by wrapping the formula in IFERROR without investigating: that may hide a data issue instead of resolving it. If a calculation does not update after edits, check Formulas > Calculation Options and select Automatic if that is the intended workbook setting.

Average, median, and choosing a useful summary

The arithmetic mean is useful when values are on a comparable scale and every observation should count equally. It can be pulled toward extreme values. For example, a group of four values—20, 22, 24, and 134—has an average of 50, although three of the four observations are close to the low twenties. In that situation, the median may better represent a typical observation.

With values in B2:B5, enter =MEDIAN(B2:B5) to calculate the median, which is 23 for 20, 22, 24, and 134. With those same values, enter =AVERAGE(B2:B5) to calculate the mean, which is also 50. The difference is not an Excel error; the two functions answer different questions. Consider showing both measures when readers need to understand a skewed distribution, and explain which one supports the decision or comparison.

Frequently Asked Questions

What is the formula for average in Excel?
Use =AVERAGE(range), replacing range with the cells to calculate, such as =AVERAGE(B2:B10). The function returns the arithmetic mean of the numeric values it includes.

Does Excel AVERAGE count blank cells?
No. Empty cells in a referenced range are ignored, so they do not increase the count used to calculate the mean. Numeric zeros are counted, which is why a blank and a zero can produce different results.

How do I average only numbers above zero?
Use AVERAGEIF with a greater-than-zero criterion. If the data is in B2:B10, enter =AVERAGEIF(B2:B10,">0"). Use this only when zero values should genuinely be excluded from the calculation.

How do I average cells that match a category?
Use AVERAGEIF. Put the category to match in a cell such as D2, then use =AVERAGEIF(A2:A10,D2,B2:B10) when A2:A10 contains categories and B2:B10 contains the numbers to average.

Why does my average show an error?
Check for error values in the selected range, an invalid or misaligned criteria range, or an average calculation with no matching numeric values. Correct the underlying issue or use a deliberate error-handling formula with a useful message.

Can I average only visible rows in a filtered list?
Yes. For a filtered range in B2:B20, =SUBTOTAL(1,B2:B20) calculates the average of visible rows by excluding rows hidden by the filter. Use =SUBTOTAL(101,B2:B20) if manually hidden rows should be excluded too.

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.