AVERAGE excluding #N/A
 

I have read through most of posts that seem to apply but haven't found a
formula that will allow me to calculate the AVERAGE, MAX and MIN for a data
range that also contains #N/A in several cells. Can you help?
--
RonB

Hi!

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

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

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

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

Biff

-----Original Message-----
I have read through most of posts that seem to apply but
haven't found a
formula that will allow me to calculate the AVERAGE, MAX
and MIN for a data
range that also contains #N/A in several cells. Can you
help?
--
RonB
.

Works great...... Thanks!

"Biff" wrote:

Hi!

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

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

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

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

Biff

-----Original Message-----
I have read through most of posts that seem to apply but
haven't found a
formula that will allow me to calculate the AVERAGE, MAX
and MIN for a data
range that also contains #N/A in several cells. Can you
help?
--
RonB
.

You should always try to fix errors at source rather than compensating for
them.

=IF(ISNA(your_formula),0,your_formula) or perhaps
=IF(ISNA(your_formula),"",your_formula)

will prevent the NAs appearing in the first place

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"RonB" wrote in message
...
Works great...... Thanks!

"Biff" wrote:

Hi!

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

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

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

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

Biff

-----Original Message-----
I have read through most of posts that seem to apply but
haven't found a
formula that will allow me to calculate the AVERAGE, MAX
and MIN for a data
range that also contains #N/A in several cells. Can you
help?
--
RonB
.