How do I average a range of cells when one cell contains #N/A
 

I perform a lookup where the results could populate one cell or as many as 15
cells with number results. The cells that do not result in numbers have
#N/A. I want to average the fifteen cells but only the cells with numbers.
How do I get it to ignore the #N/A when performing the average?

=AVERAGE(IF(NOT(ISNA(A1:A15 )),A1:A15))

which is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

"hongkonglt" wrote in message
...
I perform a lookup where the results could populate one cell or as many as
15
cells with number results. The cells that do not result in numbers have
#N/A. I want to average the fifteen cells but only the cells with
numbers.
How do I get it to ignore the #N/A when performing the average?

On Sun, 18 Sep 2005 17:08:02 -0700, "hongkonglt"
wrote:

I perform a lookup where the results could populate one cell or as many as 15
cells with number results. The cells that do not result in numbers have
#N/A. I want to average the fifteen cells but only the cells with numbers.
How do I get it to ignore the #N/A when performing the average?

Use this **array** formula:

=AVERAGE(IF(NOT(ISNA(rng)),rng))

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.


--ron

Another way:

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

Biff

"hongkonglt" wrote in message
...
I perform a lookup where the results could populate one cell or as many as
15
cells with number results. The cells that do not result in numbers have
#N/A. I want to average the fifteen cells but only the cells with
numbers.
How do I get it to ignore the #N/A when performing the average?