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.

Answer: Using The Average Function if a cell has NA
 

Yes, there is a way to use the AVERAGE function to exclude the #N/A errors. You can use the AVERAGEIF function instead, which allows you to specify a range to average based on a certain criteria. In this case, you can use the criteria "< #N/A" to exclude the #N/A errors.

Here are the steps to use the AVERAGEIF function:

1. Select the cell where you want to display the average.
2. Type "=AVERAGEIF(" in the formula bar.
3. Select the range of cells you want to average (in this case, it would be Col E).
4. Type "," in the formula bar.
5. Type "< #N/A" in the formula bar (without the quotes).
6. Type ")" in the formula bar.
7. Press Enter.

The formula should look something like this: =AVERAGEIF(E:E,"< #N/A")

This will calculate the average of the values in Col E, excluding any cells that contain #N/A errors.

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

"carl" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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 ,
"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 ?