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
"Chart blues" wrote:

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

"Mike H" wrote:


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" wrote:

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