Thread: #N/A

Surprising Zeros in Charts

My model is built.
Using #N/A - my chart displays perfectly, zeros suppressed.
This formula is what feeds the values in my chart series.
=IF(ISERROR(((AVERAGE(SHEET1!BE2:BM2))*(100/5))),””,((AVERAGE(SHEET1!BE2:BM2))*(100/5)))

If I alter that error handling
to:=IF(ISERROR(((AVERAGE(SHEET1!BE2:BM2))*(100/5))),"#N/A",((AVERAGE(SHEET1!BE2:BM2))*(100/5)))
Zero still displays…
If I may ask – how would I alter this formula to function they way I hope it
would?

Sincerely,
Arturo

Try...

=IF(COUNT(Sheet1!BE2:BM2),AVERAGE(Sheet1!BE2:BM2)*100/5,#N/A)

Hope this helps!

In article <D8F9D3CC-A082-42CE-8654-5A6EA7A6C821@microsoft.com>,
Arturo <Arturo@discussions.microsoft.com> wrote:

> Surprising Zeros in Charts
>
> My model is built.
> Using #N/A - my chart displays perfectly, zeros suppressed.
> This formula is what feeds the values in my chart series.
> =IF(ISERROR(((AVERAGE(SHEET1!BE2:BM2))*(100/5))),””,((AVERAGE(SHEET1!BE2:BM2))
> *(100/5)))
>
> If I alter that error handling
> to:=IF(ISERROR(((AVERAGE(SHEET1!BE2:BM2))*(100/5))),"#N/A",((AVERAGE(SHEET1!BE
> 2:BM2))*(100/5)))
> Zero still displays…
> If I may ask – how would I alter this formula to function they way I hope it
> would?
>
> Sincerely,
> Arturo

It may be treating #N/A as a string not the #N/A value. You may try this one:
=IF(ISERROR(((AVERAGE(SHEET1!BE2:BM2))*(100/5))),NA(),((AVERAGE(SHEET1!BE2:BM2))*(100/5)))

Hope this helps,
Miguel.

"Arturo" wrote:

> Surprising Zeros in Charts
>
> My model is built.
> Using #N/A - my chart displays perfectly, zeros suppressed.
> This formula is what feeds the values in my chart series.
> =IF(ISERROR(((AVERAGE(SHEET1!BE2:BM2))*(100/5))),””,((AVERAGE(SHEET1!BE2:BM2))*(100/5)))
>
> If I alter that error handling
> to:=IF(ISERROR(((AVERAGE(SHEET1!BE2:BM2))*(100/5))),"#N/A",((AVERAGE(SHEET1!BE2:BM2))*(100/5)))
> Zero still displays…
> If I may ask – how would I alter this formula to function they way I hope it
> would?
>
> Sincerely,
> Arturo