To calculate the average value of data in Excel, use the AVERAGE function, which sums the selected numbers and divides by the count of numeric cells. For example, =AVERAGE(A1:A10) returns the arithmetic mean of the values in cells A1 through A10. This article covers the main average functions, how to handle blanks, text, and errors, and when to use median or mode instead.
Why “Average” Can Mean Different Things
In everyday language, “average” usually means the arithmetic mean, but Excel offers several related functions that answer different questions. The mean is the sum divided by the count, the median is the middle value when data is sorted, and the mode is the most frequently occurring value. Each is useful in different situations. For instance, if you have a list of household incomes, the mean can be skewed by a few very high values, while the median gives a better sense of a typical income. Excel’s AVERAGE, MEDIAN, and MODE functions let you switch between these measures easily.
Using the AVERAGE Function
The AVERAGE function is the most direct way to calculate the mean of a range. Its syntax is:
=AVERAGE(number1, [number2], ...)You can reference individual cells, ranges, or typed numbers. For example:
=AVERAGE(B2:B20)averages all numeric values in that range.=AVERAGE(5, 10, 15)returns 10.=AVERAGE(A1, C1, E1)averages three non-adjacent cells.
The function ignores empty cells, text, logical values (TRUE/FALSE), and error values within the range. However, if you include a cell containing text directly as an argument (e.g., =AVERAGE("5", 10)), Excel will try to convert it to a number, which can cause unexpected results. It is safer to reference cells rather than typing values.
Dealing with Blank Cells and Zeros
A common misunderstanding is that AVERAGE treats blank cells as zero. It does not—blank cells are ignored. Only cells containing the number 0 are included and will lower the average. If you need to include blanks as zeros, you must either fill them with 0 or use a custom formula like =SUM(range)/COUNTA(range) (which counts all non-empty cells, including text). For example, if A1:A5 contains 10, 20, blank, 30, 40, =AVERAGE(A1:A5) returns 25 (100/4), while =SUM(A1:A5)/COUNTA(A1:A5) returns 20 (100/5).
Calculating Average with Conditions: AVERAGEIF and AVERAGEIFS
Often you need the average of only certain values, such as sales above a threshold or data for a specific category. Excel provides AVERAGEIF for a single condition and AVERAGEIFS for multiple conditions.
=AVERAGEIF(range, criteria, [average_range]) averages cells that meet the criteria. For example, =AVERAGEIF(B2:B20, ">100") averages only values greater than 100. If you want to average a different column based on a condition in another, use the optional third argument: =AVERAGEIF(A2:A20, "West", B2:B20) averages B values where column A says “West”.
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) works similarly but allows multiple conditions. For instance, =AVERAGEIFS(B2:B20, A2:A20, "West", C2:C20, ">=2023") averages sales for West region in 2023 or later.
Median and Mode: Alternatives to the Mean
When your data has outliers or is skewed, the median may be more representative. Use =MEDIAN(number1, [number2], ...) to find the middle value. For example, =MEDIAN(A1:A10) returns the median of those 10 numbers. If the count is even, Excel averages the two middle values.
The mode is the most frequent value. In modern Excel (365 and 2021), use =MODE.SNGL(range) for a single mode, or =MODE.MULT(range) to return an array of all modes. Older versions use =MODE(range). For example, =MODE.SNGL(A1:A10) returns the most common number in that range.
Handling Errors and Text in Averages
If your data contains error values like #DIV/0! or #N/A, the AVERAGE function will return an error. To ignore errors, you can use =AGGREGATE(1, 6, range) where 1 means AVERAGE and 6 means “ignore error values”. For example, =AGGREGATE(1, 6, A1:A10) averages the numeric cells while skipping errors. Alternatively, you can use =AVERAGE(IF(ISNUMBER(A1:A10), A1:A10)) as an array formula (press Ctrl+Shift+Enter in older Excel) to average only numbers, ignoring errors and text.
Common Mistakes and Pitfalls
Several frequent errors occur when calculating averages:
- Including header cells: If you select a range that includes a text header, Excel ignores it, but if you include a numeric label, it will be counted. Always check your range.
- Using AVERAGE on hidden rows:
AVERAGEincludes hidden rows. If you filter data and want the average of visible cells only, use=SUBTOTAL(1, range)(101 for AVERAGE in newer versions). - Confusing mean with median: For skewed data, the mean can be misleading. Always consider the distribution.
- Rounding prematurely: If you round each value before averaging, the result may differ from averaging the unrounded numbers. Use Excel’s
ROUNDfunction only on the final result if needed.
Frequently Asked Questions
How do I average only non-blank cells?
Use =AVERAGE(range) as it already ignores blanks. If you need to include blanks as zeros, use =SUM(range)/COUNTA(range) where COUNTA counts all non-empty cells, including text.
Why does my AVERAGE return #DIV/0!?
This error occurs when the range contains no numeric values—only text, blanks, or errors. Check that your range includes at least one number. If you have error values, use AGGREGATE to ignore them.
How do I average based on multiple conditions?
Use AVERAGEIFS with the syntax =AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2). For example, =AVERAGEIFS(B2:B20, A2:A20, "North", C2:C20, ">100") averages B values where column A is “North” and column C is greater than 100.
What is the difference between AVERAGE and AVERAGEA?
AVERAGE ignores text and logical values, while AVERAGEA includes text (as 0) and logical values (TRUE=1, FALSE=0). Use AVERAGE for numeric data, and AVERAGEA only if you intentionally want to count text as zero.
Can I average cells with errors without deleting them?
Yes, use =AGGREGATE(1, 6, range) to average while ignoring errors. Alternatively, use an array formula like =AVERAGE(IF(ISNUMBER(range), range)) and confirm with Ctrl+Shift+Enter in older Excel versions.