Using The Average Function if a cell has NA

I have a formula in Col A,B,C,D that sometimes returns #N/A. I would like to
average the numbers in Col E but the average function returns #N/A.

Is there a way to use the average function to exclude the NA's ?

Thank you in advance.

=AVERAGE(IF(ISNUMBER(A1:A5),A1:A5))
entered with CTRL SHIFT ENTER.

"carl" <[email protected]> wrote in message
news:[email protected]...
> I have a formula in Col A,B,C,D that sometimes returns #N/A. I would like
to
> average the numbers in Col E but the average function returns #N/A.
>
> Is there a way to use the average function to exclude the NA's ?
>
> Thank you in advance.

=SUMIF(Range,"<>#N/A")/COUNT(Range)

carl wrote:
> I have a formula in Col A,B,C,D that sometimes returns #N/A. I would like to
> average the numbers in Col E but the average function returns #N/A.
>
> Is there a way to use the average function to exclude the NA's ?
>
> Thank you in advance.

Hi Carl,

Try

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

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"carl" <[email protected]> wrote in message
news:[email protected]...
> I have a formula in Col A,B,C,D that sometimes returns #N/A. I would like
to
> average the numbers in Col E but the average function returns #N/A.
>
> Is there a way to use the average function to exclude the NA's ?
>
> Thank you in advance.

=AVERAGE(IF(NOT(ISNA(A1:A10)),A1:A10)) array entered

"carl" <[email protected]> wrote in message
news:[email protected]...
>I have a formula in Col A,B,C,D that sometimes returns #N/A. I would like
>to
> average the numbers in Col E but the average function returns #N/A.
>
> Is there a way to use the average function to exclude the NA's ?
>
> Thank you in advance.

You've been given solutions for what you asked for, but in general, it's
poor practice to return expected "errors" as errors. If you trapped the
NA error, returning a text or null string instead, you could use
Average() as is. For instance, if the #N/A is being generated by a
VLOOKUP():

=VLOOKUP(A1,J:K,2,FALSE)

You could trap the error:

=IF(ISNA(MATCH(A1,J:J,FALSE)),"Not Found",VLOOKUP(A1,J:K,2,FALSE))

and Average will ignore the text...

In article <[email protected]>,
"carl" <[email protected]> wrote:

> I have a formula in Col A,B,C,D that sometimes returns #N/A. I would like to
> average the numbers in Col E but the average function returns #N/A.
>
> Is there a way to use the average function to exclude the NA's ?