Average in Excel: Formula, Examples, and Common Fixes

Formulas & functions · Updated November 21, 2026

To calculate an average in Excel, use the AVERAGE function: if the numbers are in A1:A10, enter =AVERAGE(A1:A10) in an empty cell and press Enter. Excel adds the numeric values in that range and divides their sum by how many numeric values it contains.

What the AVERAGE function does

An average, also called the arithmetic mean, gives a single value representing the center of a group of numbers. Excel calculates it by summing the numbers and dividing by their count. For example, the average of 10, 20, and 30 is 20: their sum is 60, and there are three values.

The function can take individual numbers, cell references, or ranges. To average several separate cells, with values in A1, A3, and A5, enter =AVERAGE(A1,A3,A5). The comma-separated arguments tell Excel which cells to include. You can also combine ranges and individual references in one function.

For most worksheet calculations, use a range reference rather than typing the numbers directly into the formula. A range updates automatically when its cell values change, making the result easier to maintain. Excel recalculates the result when an included value changes, provided calculation is set to Automatic, the usual default.

A simple example with a range

Suppose A2:A6 contains the values 12, 15, 18, 21, and 24. Their sum is 90, and there are five numbers, so the average is 18. With those values in A2:A6, enter this formula in another cell: =AVERAGE(A2:A6). The result is 18.

To build the example in a worksheet:

  1. Type the five numbers into cells A2 through A6, one number per cell.
  2. Select an empty cell for the answer, such as A7.
  3. Type the formula using the A2:A6 range and press Enter.

The colon in a reference such as A2:A6 means every cell from A2 through A6, including both endpoints. If you add a value in A7, it will not be included in the existing range; edit the range if you want the new cell included. To make a formula easier to copy down a table, keep the data in a consistent column and make sure each formula refers to the intended rows.

How blanks, text, and zeros are treated

AVERAGE includes numeric values and ignores empty cells and text entries in referenced cells. A cell containing the text word missing is not counted as a number. This can be useful when data has not been entered yet, but it also means that a range with only a few numeric entries can produce an average that looks more representative than it is.

Zero is different from a blank: Excel counts a numeric zero in the calculation. For example, if a range contains 10, 20, and 0, the average is 10, not 15, because the total of 30 is divided by three values. Do not replace unknown or unavailable measurements with zero unless zero is genuinely the intended value. Use a blank or a clearly documented missing-data convention when the value is unknown.

A number stored as text may also be excluded from a referenced range. A value such as 25 that is left-aligned or marked with a warning indicator may have been imported as text. Check the cell format and the source data, then convert it to a numeric value if appropriate. Simply changing the display format does not always convert text into a number. Errors in referenced cells, such as #N/A or #VALUE!, generally cause the average formula to return an error too; find and resolve the source issue rather than assuming the function skipped it.

Average only values that meet a condition

When you need an average for a subset of data, use AVERAGEIF for one condition or AVERAGEIFS for multiple conditions. For instance, suppose department names are in A2:A6 and corresponding scores are in B2:B6. To average scores for the Sales department, with those columns and rows in place, use =AVERAGEIF(A2:A6,"Sales",B2:B6). The first range is checked for the condition, and the final range supplies the numbers to average.

If the matching records have scores of 80 and 90, the result is 85. The condition can also use a comparison. With scores in B2:B20, to average only scores greater than or equal to 70, enter =AVERAGEIF(B2:B20,">=70"). The criterion is text inside quotation marks even though it represents a numeric test.

For conditions such as a department and a date period together, use AVERAGEIFS. Its average range comes first, followed by each criteria range and criterion pair. Confirm that the ranges have matching dimensions; mismatched ranges can lead to incorrect results or errors. If no cells meet the condition, Excel returns a divide-by-zero error because there are no qualifying numbers to average.

Calculate a weighted average

A regular average gives every value equal influence. A weighted average gives more influence to values assigned larger weights. This is useful for examples such as a course grade where a final exam counts more than a quiz, or a unit price where purchases have different quantities. The weights should correspond to the values and should usually add up to 100% (or to 1 when expressed as decimals).

Suppose scores are in B2:B4 and their weights are in C2:C4: scores of 80, 90, and 70 have weights of 20%, 30%, and 50%. With scores in B2:B4 and corresponding weights in C2:C4, enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). The result is 79: 80 × 20% plus 90 × 30% plus 70 × 50% equals 79, and the weights total 100%.

Dividing by the sum of the weights makes the calculation work even if the weights are entered as 20, 30, and 50 rather than percentages. Check that the values and weights are aligned row by row, and decide how to handle missing values before calculating. A blank score paired with a nonzero weight should not silently be treated as a meaningful score; inspect the data and adjust the included rows if necessary.

Common problems and how to fix them

  • The result is unexpectedly low. Check for zeros that represent missing data, and inspect whether the range includes extra cells such as totals or placeholder values.
  • The result is unexpectedly high. Look for numbers stored as text that were excluded, or a range that omits valid lower values.
  • Excel shows an error. Check the referenced cells for errors and, with a conditional average, confirm that at least one value meets the criteria.
  • The formula appears as text. The cell may be formatted as Text, or the formula may have been entered with an apostrophe before the equals sign. Change the cell format to General, then re-enter the formula.
  • The result does not change. Check that calculation is set to Automatic under Formulas > Calculation Options. If calculation is manual, press F9 to recalculate the workbook.

Also check the selected range carefully. A formula in a row directly below the data can be convenient, but it is easy to include that result cell in a later calculation by mistake. For a large or frequently changing dataset, an Excel table can make references easier to read and can expand as rows are added. Confirm that any new rows really belong in the average before relying on an updated result.

Mean, median, and other ways to summarize data

The average calculated by AVERAGE is the mean. It is useful when values are reasonably balanced, but it can be pulled strongly by unusually large or small observations. Consider incomes of 30,000, 32,000, 34,000, and 200,000. The mean is 74,000, while the median—the middle value after sorting—is 33,000. Neither number is inherently wrong; they answer different questions.

Use the mean when the total across all observations matters, such as average units produced per day across a period. Consider the median when you want a typical middle observation and outliers would distort the mean, as can happen with prices or waiting times. You can report both when the distribution is uneven. Always state what is being averaged and over what period or population; an average without that context can be misleading.

For a median, with the same numeric observations in A2:A6, enter =MEDIAN(A2:A6). The median function ignores text and blank cells in a referenced range, but—as with an average—zeros are numeric values and count. If your question is about the most frequently occurring value rather than the center, a mode may be more relevant than either mean or median.

Averaging dates and times

Excel stores dates and times as numeric values, so it can average them. The result is a midpoint in Excel’s date-and-time number system, which may be useful for finding an average date or time from a consistent set of entries. With valid dates in A2:A10, enter =AVERAGE(A2:A10). Format the result cell as a date using Home > Number Format, or choose a date format from the Number Format list, so the underlying result is displayed in a readable way.

For times, ensure every entry uses a consistent interpretation. A time-only average around midnight can be confusing: for example, 11:50 PM and 12:10 AM are close together in real time, but a simple numeric mean can produce noon. If records cross midnight, decide how the times should be represented and interpreted before averaging. Dates or times stored as text will not be treated like valid numeric date/time entries by the function.

Frequently Asked Questions

What is the Excel formula for average?
Use =AVERAGE(range), replacing range with the cells to calculate. For example, with numbers in B2:B12, enter =AVERAGE(B2:B12).

Does Excel average ignore blank cells?
Yes. AVERAGE ignores blank cells in a referenced range, but it counts numeric zeros. Text in referenced cells is also ignored, while an error in the range can make the result an error.

How do I average a column without counting zeros?
Use AVERAGEIF with a not-equal-to-zero criterion. With values in B2:B20, enter =AVERAGEIF(B2:B20,"<>0"). This excludes zeros but still averages other numeric values in the range.

How do I average filtered rows only?
AVERAGE includes values in rows hidden by a filter. For a filtered list, use SUBTOTAL with function number 1, which calculates an average while excluding filtered-out rows. With values in B2:B20, enter =SUBTOTAL(1,B2:B20). It includes manually hidden rows; function number 101 excludes both filtered-out and manually hidden rows.

Why does AVERAGE return #DIV/0!?
Excel returns #DIV/0! when there are no numeric values to average, which can happen if the range is blank or all entries are text, or if no entries meet an average condition. Check the data type, range, and criteria.

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.