software

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--
rmeister
------------------------------------------------------------------------
rmeister's Profile: www.excelforum.com/member.php...oamp;userid=30163
View this thread: www.excelforum.com/showthread...hreadid=505321
{=IF(ISERROR(AVERAGE(IF('Drop'!$A$2:$A$65563='CE
OHIO'!B3,'Drop'!$AC$2:$AC65536))),0,AVERAGE(IF('Dr op'!$A$2:$A$65563='CE
OHIO'!B3,'Drop'!$AC$2:$AC65536)))}Cheers,

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=505321gt; Does
gt; anyone know how to return 0 if there is an error like this.

quot;yourformulaquot; gives an error. use a structure like this to prevent any
error:

=if(iserror(yourformula),quot;your error valuequot;,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
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(quot;data rangequot;=0,quot;your error valuequot;,yourformula)

where quot;data rangequot; 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

quot;arnoquot; wrote:

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

gt; =if(sum(quot;data rangequot;=0,quot;your error valuequot;,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
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.--
tony h
------------------------------------------------------------------------
tony h's Profile: www.excelforum.com/member.php...oamp;userid=21074
View this thread: www.excelforum.com/showthread...hreadid=505321Fair point Arno, should probably have been clearer and a missing bracket
doesn't help
=if(sum(quot;data rangequot;)=0,quot;your error valuequot;,yourformula)

In this case the error is caused by having no data in the quot;data rangequot; 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$1gt;=CM$7),VLOOKUP($A27,'[Flows Data Sheet.xls]1105
Inv'!$A$4:$HH$1000,'TS'!CM$8,0),(CM36-CM31 CM40 CM41)))lt;0,0,(IF(($B$1gt;=CM$7),VLOOKUP($A2 7,'[Flows
Data Sheet.xls]1105
Inv'!$A$4:$HH$1000,'TS'!CM$8,0),(CM36-CM31 CM40 CM41))))),0,(IF((IF(($B$1gt;=CM$7),VLOOKUP ($A27,'[Flows
Data Sheet.xls]1105
Inv'!$A$4:$HH$1000,'TS'!CM$8,0),(CM36-CM31 CM40 CM41)))lt;0,0,(IF(($B$1gt;=CM$7),VLOOKUP($A2 7,'[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$1gt;= CM$7),VLOOKUP($A27,'[Flows
Data Sheet.xls]1105
Inv'!$A$4:$HH$1000,'TS'!CM$8,0),(CM36-CM31 CM40 CM41)))lt;0,0,(IF(($B$1gt;=CM$7),VLOOKUP($A2 7,'[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.

Gizquot;arnoquot; wrote:

gt; gt; =if(sum(quot;data rangequot;=0,quot;your error valuequot;,yourformula)
gt;
gt; ??
gt;
gt; pls. explain with
gt;
gt; =if(iserror(hlookup(a1,myrange,2,false)),0,hlookup (a1,myrange,2,false))
gt;
gt; or
gt;
gt; =if(iserror(a1/b1),0,a1/b1) where b1 can be 0, #N/V, #Div0 or #Name
gt;
gt; arno
gt;
gt;