Averaging with #N/A Cells

[tylermdsm]

i'm trying to average 5 cells. One has an #n/a value. This causes the
average to say #n/a. Is there a way to correct this?

 

[Chip Pearson]

Use the following array formula:

=AVERAGE(IF(ISNA(A1:A5),FALSE,A1:A5))

Of course, change both occurrences of A1:A5 to your actual range.

Since this is an array formula, you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula in the formula bar
enclosed in curly braces { }. See www.cpearson.com/Excel/ArrayFormulas.aspx
for much more information about array formulas.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Max]

One way
In B1, array-enter by pressing CTRL+SHIFT+ENTER,
instead of just pressing ENTER:
=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))

 

[David Biddulph]

=AVERAGE(IF(ISNA(A1:A5),"",A1:A5)) entered as an *array formula* (Control
Shift Enter).

 

[T. Valko]

One more:

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