Averaging with #N/A Cells
 

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?

Averaging with #N/A Cells
 

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)


"tylermdsm" wrote in message
...
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?

Averaging with #N/A Cells
 

One way
In B1, array-enter by pressing CTRL+SHIFT+ENTER,
instead of just pressing ENTER:
=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"tylermdsm" wrote:
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?

Averaging with #N/A Cells
 

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

"tylermdsm" wrote in message
...
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?

Averaging with #N/A Cells
 

One mo

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


--
Biff
Microsoft Excel MVP


"tylermdsm" wrote in message
...
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?