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.

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

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),"")

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.