Empty cells plotted as zero.

[ColinC]

In sheet 3 there is a column of figures (col. C)which builds up over time. When a group of four consecutive cells all have values in them the values are averaged and appear in cell P9 in sheet 4

=IF(COUNTBLANK(C13:C16)>0,"",AVERAGE(C13:C16))

This is working O.K. I now want to plot these averages but have the problem of empty cells being plotted as zero instead of being ignored.



=IF(Sheet3!P9>0,Sheet3!P9,NA())

I have had this problem before and have received advice from this forum concerning the NA() function. It has worked O.K. on similar programs but not this time. Help please.

Colin.

[OTPM]

Hi Colin
Go to FILE > OPTIONS > ADVANCED
And untick the "Show a Zero in cells that have a zero value.
Good luck.
Tony

[Kevin@Radstock]

Hi ColinC

Try the averageif within the iferror.
FYI, you can format/pre-format a selection of cells to not show zero values. For example: General;;" or "0.000;;" etc. That way you can still see zero values elsewhere if required.

=IFERROR(AVERAGEIF(C13:C16,">0",C13:C16),"")

[ColinC]

Thanks Tony. Tried unticking but hasn't made any difference.
The problem as I see it is in treating an empty cell as if it did contain zero.
Colin.