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" <ronb@discussions.microsoft.com> wrote in message
news:165E80C7-2317-4B3E-9C81-DE79B1D89EDA@microsoft.com...
> 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
> > >.
> > >
> >