ignore #NA in average formula

  • Thread starter Thread starter Chart blues
  • Start date Start date
C

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
 
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
 
Mike: Thank you for the tip... it did the job!

Mike H said:
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
Click to expand...
 
Back
Top