ignore #NA in average formula

[Chart blues]

I'm plotting daily results in a data table which sometimes have 0 values. I
have created a formula which replaces the 0 values with #NA
ie:=IF(B6,B6,NA()) . This throws off the average formula at the end of the
table. Can someone suggest a solution, please?

Thanks

Rosina

 

[Mike H]

Hi,

Try this and change the range to suit

=AVERAGE(IF(ISNUMBER(A1:A5),A1:A5,FALSE))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
just enter. If you do it correct then Excel will put curly brackets around
the formula{}. You can't type these yourself.

Mike

 

[Chart blues]

Mike: Thank you for the tip... it did the job!

Hi,

Try this and change the range to suit

=AVERAGE(IF(ISNUMBER(A1:A5),A1:A5,FALSE))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
just enter. If you do it correct then Excel will put curly brackets around
the formula{}. You can't type these yourself.

Mike