## AVERAGE ignore blanks

*Article last 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?

Calculate average of last 10 data with possible blank cells

Question: Answer: This array formula creates a dynamic range, filtering the 10 last data. Adjust cell ranges $A$1:$A$25 in formula below. […]

How to use the AVERAGE function

Calculates the average of numbers in a cell range. In other words, the sum of a group of numbers and […]

The array formula in cell D3 calculates an average and ignores 0 (zeros). =AVERAGE(IF(B3:B8<>0,B3:B8,"")) The formula above is an array […]

Column C contains both date and time, to calculate the average of only time we need to extract the hours, minutes […]

#N/A error is sometimes used to show gaps in charts, however, the AVERAGE function can't process errors, shown in C11 […]

The array formula in cell D14 calculates an average based on multiple criteria in cell range B14:B15. If value in […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form