Average with a #N/A

The #N/A is not a literal value, so you cannot find it using "<>#n/a".
There is an ISNA function you can use (see: MS Excel: ISNA Function (WS)).
But I usually, I prefer to address it right at the source. I am guessing that it is some formula that is returning the #N/A errors, right?
What is that function? Usually, the function can be modified so it doesn't return #N/A.

It is possible to use "<>#n/a" as a legitimate criterion to exclude #N/A values in an AVERAGEIF/AVERAGEIFS function, e.g. this is valid

=AVERAGEIFS($F$8:$F$372,$A$8:$A$372,"<>#n/a")

.....but the rest of your formula seems garbled - is it displaying as it should?

It is possible to use "<>#n/a" as a legitimate criterion to exclude #N/A values in an AVERAGEIF/AVERAGEIFS function, e.g. this is valid

=AVERAGEIFS($F$8:$F$372,$A$8:$A$372,"<>#n/a")

Click to expand...

Ah, maybe where I am getting confused is when people try to find it as literal text (they put the "#n/a" in double-quotes). My bad.
In any event, I still prefer to handle the errors at the root, whenever possible. Keeps things cleaner that way.

Joe4 - Thank you for your reply.
I am using it for a monthly average.
Here is the example of what I am doing.
This is where the cell is drawing the data from.
Column
---A-------B---
1---7-1-15---400
2---7-2-15---350
3---7-3-15---200
4---7-4-15---#N/A
5---7-5-15---#N/A
6---7-6-15---#N/A

I have Conditional Formatting set up "Formula: ISNA=(B1) Applies to $B$1:$B$30
It returns a zero number. It should return the average of 317.

I thought this would work?
I am doing something wrong.

Thank you for your help.


The cell shows a zero number

barry houdini - Thank you for your reply.

With the rest of the formula I have been trying to have it return a zero
if no numbers were entered in the cell.
I also have the daily data on a chart and I have it set up
so the readings on the chart will not drop to zero if no data is entered on the cells
of the month.

Thank you for your help.

I have Conditional Formatting set up "Formula: ISNA=(B1) Applies to $B$1:$B$30

Click to expand...

That would need to be:
Formula: =ISNA(B1)

Back to my original thought. So you have #N/A be returned in cells B4:B6.
I am guessing those are results of formulas.
Exactly what is the formula found in cell B4?

Joe4 - Thank you for your help.

I was trying to make the example not to complex but here is the actual sheet.
I have four columns (A)Date, (B) Time, (C) first reading, (D) second reading, (E) third reading and the (F) highest of the three readings (IF(C184<1,#N/A,MAX(C184:E184))
This would be the (B4) in my example.

Thank you for your help.

OK, after playing around and doing some testing with it, I believe Barry is correct. I don't think the #N/A part of the function is the problem, I think it is what comes after it. It seems very confusing, and we are not sure what it is you are trying to do here.

For example, this part:
does not seem to make much sense. For one thing, you have double quotes at the end of (what looks like) your date value, but not at the beginning. So that right there is invalid syntax. Even if you did enclose the whole thing in quotes, then it would be treated as Text and not a Date value.

It may be best to just try to explain to use in plan English exactly what you are trying to do with this formula.

Joe4 - Thank you for your help.
I have a sheet that I post three readings day after day (Col C thru E) and I have col F extract the highest reading (IF(C184<1,#N/A,MAX(C184:E184))
and since it flows to a chart I have the #N/A in the formula so the chart well not drop to zero (Reading points - line chart)
I then have a row under the chart that have the average month by month IF(ISERROR(AVERAGEIFS($F$8:$F$372,$A$8:$A$372,"<8-1-2015",$A$8:$A$372,">6-30-2015")),"0",AVERAGEIFS($F$8:$F$372,$A$8:$A$372,"<8-1-2015",$A$8:$A$372,">6-30-2015")), when the month has no N/A in it the formula will work.
But when the month is not complete the it can not average the month because of the N/A

So the N/A prevents the chart line points from dropping to zero but prevents a average from being calculated.

Thank you very much for your help