If it returns #N/A, making it return 0

[jayhawk1919]

I have a list of numbers, that at the end of their row it averages them.
However, the numbers are obtained by a VLOOKUP function. If it can't
find what it's looking for, it returns #N/A which makes it so it can't
calculate the average at the end. Is there any way to get the VLOOKUP
function to return 0 instead of #N/A? Or maybe make the cell return 0
if the VLOOKUP function returns #N/A?

 

[jayhawk1919]

Also, if it returns #REF! to do the same thing.


Thanks.

 

[Peo Sjoblom]

A couple of ways, sometimes it could be good to know
where the errors are and you can get average like this
and still keep the errors

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

or

=AVERAGE(IF(ISNUMBER(Range),Range))

the latter entered with ctrl + shift & enter

or you can change the vlookup to something like

=IF(ISNUMBER(MATCH(A2:A50,0)),VLOOKUP(E1,A2:B50,2,0),"")

then use =AVERAGE(B2:B50) since average won't include blank cells

 

[jayhawk1919]

Hmm, well the first suggestion worked, except it counts blank cells in
the range as 0, instead of ignoring them as a regular AVERAGE function
does. Any way around this?

 

[Peo Sjoblom]

not this one

=AVERAGE(IF(ISNUMBER(Range),Range))

aray entered

btw, why would you have blank cells in a list derived from a vllookup
I thought you had either a value or #N/A? If vlookup returns a value from an
empty cell
it will return a zero

 

[jayhawk1919]

It's rather complicated, but basically there are gonna be 68 sheets
which have statistics on them, but the information is only ready for
one sheet at certain time periods (every month, for example). So right
now the empty cells would refer to sheets that aren't there, but
instead I leave them empty. Then at the end of all 68 colums theres
the cell that shows the average of the row.


And that one you just showed me just returns 0.00.