Formula's with #N/A's

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!
 
L

Laura Cook

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

Aladin Akyurek

Also...

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

or

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ignore #N/A in a sum? 3
#DIV error 2
Sum or Average with #N/A cells 5
How to automatically calculate totals within a Y-N-N/A column 5
Trying to figure the formula 1
Add to formula to put a zero in cell 4
conditional formula with lots of conditions 8
SUM, AVERAGE and PRINT with #N/A cells 11

Top