Using The Average Function if a cell has NA

[carl]

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.

 

[Dave R.]

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

 

[Aladin Akyurek]

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

 

[Bob Phillips]

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)

 

[N Harkawat]

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

 

[JE McGimpsey]

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