How To return 0 if error

[rmeister]

I am using an array formula that looks like this:

{=AVERAGE(IF('Drop'!$A$2:$A$65563='CE OHIO'!B3,'Drop'!$AC$2:$AC65536))}

The problem is if there is no information for this formula to reference then the result is #DIV/0! This is rather ugly on a spreadsheet. Does anyone know how to return 0 if there is an error like this.

Thank you in advance for any help

[SteveG]

{=IF(ISERROR(AVERAGE(IF('Drop'!$A$2:$A$65563='CE OHIO'!B3,'Drop'!$AC$2:$AC65536))),0,AVERAGE(IF('Drop'!$A$2:$A$65563='CE OHIO'!B3,'Drop'!$AC$2:$AC65536)))}


Cheers,

Steve

[arno]

> Does
> anyone know how to return 0 if there is an error like this.

"yourformula" gives an error. use a structure like this to prevent any
error:

=if(iserror(yourformula),"your error value",yourformula)

if you want to check only special errors use instead of ISERROR eg.
ISNV for lookups, there are also other IS-functions, see online help
for details.

There's no way around this - this is the workaround.

arno

[Gizmo63]

You can make the formula length a bit more manageable by testing the
condition that will cause the error instead of repeating the whole formula.
like:
=if(sum("data range"=0,"your error value",yourformula)

where "data range" is the data being averaged.
I find this helps with long formulas using e.g. nested ifs lookups and the
like that end up using loads of brackets.

Giz

"arno" wrote:

> > Does
> > anyone know how to return 0 if there is an error like this.
>
> "yourformula" gives an error. use a structure like this to prevent any
> error:
>
> =if(iserror(yourformula),"your error value",yourformula)
>
> if you want to check only special errors use instead of ISERROR eg.
> ISNV for lookups, there are also other IS-functions, see online help
> for details.
>
> There's no way around this - this is the workaround.
>
> arno
>
>
>

[arno]

> =if(sum("data range"=0,"your error value",yourformula)

??

pls. explain with

=if(iserror(hlookup(a1,myrange,2,false)),0,hlookup(a1,myrange,2,false))

or

=if(iserror(a1/b1),0,a1/b1) where b1 can be 0, #N/V, #Div0 or #Name

arno

[tony h]

If you have a lot of these you may find it useful to create a VBA function to test and return the result eg

Function myClean(ByRef rng As Range) As Variant

If IsError(rng.Value) Then
myClean = 0
Else
myClean = rng.Value
End If

End Function

It has the advantage that you never have two copies of the formula with potential for typos in one and makes it easier to read.

[Gizmo63]

Fair point Arno, should probably have been clearer and a missing bracket
doesn't help
=if(sum("data range")=0,"your error value",yourformula)

In this case the error is caused by having no data in the "data range" so by
just checking for the existence of data to average it is possible to reduce
the length of the formula.
My suggestion is to check for the cause of the error at the basest level and
deal with it there. I have inherited some horrendous formulas that cover 4-5
rows on the screen and 60% of it can be removed by a simple error check like
the one above.
It will be different cases as with your lookup example. In your second
example I could go back to the formula being used in B1 and deal with the
cause of the error there. But the example is so short it probably wouldn't be
worth the effort.

But faced with (simplified):
=IF(ISERROR(IF((IF(($B$1>=CM$7),VLOOKUP($A27,'[Flows Data Sheet.xls]1105
Inv'!$A$4:$HH$1000,'TS'!CM$8,0),(CM36-CM31+CM40+CM41)))<0,0,(IF(($B$1>=CM$7),VLOOKUP($A27,'[Flows
Data Sheet.xls]1105
Inv'!$A$4:$HH$1000,'TS'!CM$8,0),(CM36-CM31+CM40+CM41))))),0,(IF((IF(($B$1>=CM$7),VLOOKUP($A27,'[Flows
Data Sheet.xls]1105
Inv'!$A$4:$HH$1000,'TS'!CM$8,0),(CM36-CM31+CM40+CM41)))<0,0,(IF(($B$1>=CM$7),VLOOKUP($A27,'[Flows
Data Sheet.xls]1105 Inv'!$A$4:$HH$1000,'TS'!CM$8,0),(CM36-CM31+CM40+CM41))))))

Which can be reduced to:
=IF(ISNA(VLOOKUP($A27,'[Flows Data Sheet.xls]1105
Inv'!$A$4:$HH$1000,'TS'!CM$8,0)),0,(IF((IF(($B$1>=CM$7),VLOOKUP($A27,'[Flows
Data Sheet.xls]1105
Inv'!$A$4:$HH$1000,'TS'!CM$8,0),(CM36-CM31+CM40+CM41)))<0,0,(IF(($B$1>=CM$7),VLOOKUP($A27,'[Flows
Data Sheet.xls]1105 Inv'!$A$4:$HH$1000,'TS'!CM$8,0),(CM36-CM31+CM40+CM41)))))

by simply testing the root of an error, you can why I suggest that approach.

Giz


"arno" wrote:

> > =if(sum("data range"=0,"your error value",yourformula)
>
> ??
>
> pls. explain with
>
> =if(iserror(hlookup(a1,myrange,2,false)),0,hlookup(a1,myrange,2,false))
>
> or
>
> =if(iserror(a1/b1),0,a1/b1) where b1 can be 0, #N/V, #Div0 or #Name
>
> arno
>
>