AVERAGE excluding #N/A

[Guest]

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?

 

[Biff]

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

 

[Guest]

Works great...... Thanks!

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

 

[Ken Wright]

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
----------------------------------------------------------------------------

Works great...... Thanks!