How do I ignore "#N/A" data in a column while calculating average

T

T. Valko

Try one of these:

=SUMIF(A1:A10,"<1E100")/COUNT(A1:A10)

This one is an array formula** :

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

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
S

Shane Devenshire

Hi,

Try this array entered formula

=AVERAGE(IF(ISNA(B1:B7),"",B1:B7))

being an array you must enter it by pressing Shift+Ctrl+Enter

In 2007 you can use

=AVERAGEIF(B1:B7,"<9E9")
 

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

Average Function that ignores zeros 10
Calculate Average in pivot item 6
How to make average function ignore MIN and MAX 6
Ignore errors when calculation average of multiple ranges 4
Subtotal 4
AVERAGE 2
How to omit blank cells when calculating weighted averages 20
Not Calculating 14

Top