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?