Formula's with #N/A's

  • Thread starter Thread starter Kevin McLean
  • Start date Start date
K

Kevin McLean

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!
 
=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 {}.
 
Also...

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

or

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