How do I ignore "#N/A" data in a column while calculating average
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)
--
Biff
Microsoft Excel MVP
"Tuan" wrote in message
...
How do I ignore "#N/A" data in a column while calculating average
|