Developer Forum

I have some data that I would like to take the simple
average of "=Average(C2:C30)". The problem is there are
some #N/A's in the list. I am pretty sure there is a
formula that excludes #N/A's I just can't remember it.

I do realize that I can just replace the #N/A's with
zeros, but for other reasons I need the #N/A's to stay
there.

Any ideas?

Thank you!

Kevin,

=IF(ISNA(etc ...

or more generically

=IF(ISERROR(etc...

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks

=AVERAGE(IF(ISNUMBER(A1:A29),A1:A29))

This formula must be array entered - You must press ctrl+shift+enter (as
opposed to just hitting enter) after inserting the formula and subsequently
every time you edit the formula for it to work. If you do this correctly,
Excel will display the formula enclosed in curly braces {}.

--
HTH,
Laura Cook
Appleton, WI

Also...

=SUMIF(C2:C30,"#N/A")/COUNT(C2:C30)

or

=SUMIF(C2:C30,"#N/A")/MAX(1,COUNT(C2:C30))