Article updated on December 13, 2017

The AVERAGE function is designed to ignore blank cells but there are instances where it fails. The picture above seems to have blank cells in B3:B8, however, they are counted as zeros. Why?

The cells in B3:B8 are not truly empty, select B3:B8 and press CTRL + 1 to open "Format Cells" settings.

Here we see that it has been formatted with a custom cell formatting. Values containing 0 (zeros) are hidden, to show them click on General and then OK.

If you are interested in learning more about custom formatting codes, read this article: Excel’s TEXT function explained

This will show your zeros again on your worksheet.

If this didn't do it, go to your Excel Options and then click on "Advanced".

Excel 2016 Users click on "File" on the ribbon and then click on "Options".

Find "Show a zero in cells that have zero value", is it enabled?