An Average that Factors In Blank Cells

Liz has a range of numeric values in A5:A22. Some of the cells in the range are blank. In calculating an average of the range, the blank cells are ignored. Liz would like to calculate the average that includes the blank cells, as if they contain a 0 value.

There are a surprisingly large number of approaches you can take to get the average that Liz wants. One easy way is to simply not use the AVERAGE function. Instead, remember that an average is calculated by deriving a sum of the cells divided by the number of cells being summed. Thus, if the range that Liz wants to average is always A5:A22, then this formula will work:

=SUM(A5:A22)/18

If it would be some different range, then the formula could be constructed in this manner:

=SUM(A5:A22)/ROWS(A5:A22)

The key is simply to replace the range referenced in the SUM and ROWS functions with the desired range. This will not work well, though, if some cells in the range could contain text and you don't want those cells factored into the average. In that case, the following variation will work:

=SUM(A5:A22)/(COUNT(A5:A22)+COUNTBLANK(A5:A22))

This works because the COUNT function returns a count of cells containing numeric values and COUNTBLANK returns, well, cells containing blanks. In other words, any cells containing text are ignored.

You could also employ the IF function in your formula, as is done in these four closely related formulas:

=AVERAGE(IF(A5:A22=0,0,A5:A22))
=AVERAGE(IF(A5:A22="",0,A5:A22))
=AVERAGE(IF(LEN(A5:A22)=0,0,A5:A22))
=AVERAGE(IF(A5:A22="",0,IFERROR(--A5:A22,"")))

If you are sure that your cells won't contain any text values, then you can use either of the following:

=AVERAGE(A5:A22 * 1)
=AVERAGE(A5:A22 + 0)

These work because the math operation intrinsic to the formula will always return a numeric value, even if the cell is blank. If you are using Excel 2019 or earlier, then these functions must be entered using Ctrl+Shift+Enter.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13963) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.