Iserror is too cumbersome as I use it - is there an alternative?

P

pzeitlin

Gang,

I have the following iserror formula:

=IF(ISERROR(IF(G36="","",(1-(IF(Inputs!B7=FC,
(HLOOKUP(reporting_period,Cost!$I$427:$HI$434,(MATCH('Deloitte
Metrics'!G36,Cost!$H$427:$H$434,0)),TRUE)),(SUMPRODUCT((Cost!$C$8:$C
$57=second_function)*(net_revenue_range))))+(IF(G36=prime_function,
(SUM('D Metrics'!$C$28:$C$31)),0)))/(SUMPRODUCT((Cost!$C$8:$C
$57=second_function)*(gross_revenue_range))))))=TRUE,0,(IF(G36="","",
(1-(IF(Inputs!B7=FC,(HLOOKUP(reporting_period,Cost!$I$427:$HI$434,
(MATCH('D Metrics'!G36,Cost!$H$427:$H$434,0)),TRUE)),(SUMPRODUCT((Cost!
$C$8:$C$57=second_function)*(net_revenue_range))))+
(IF(G36=prime_function,(SUM('D Metrics'!$C$28:$C$31)),0)))/
(SUMPRODUCT((Cost!$C$8:$C
$57=second_function)*(gross_revenue_range)))))))

This is extremely cumbersome, and in some cases, my formulas are too
long to handle in one cell with iserror. Is there another solution to
this problem? I am sure there is a VB solution, but not smart enough
on VB to create. It seems senseless to have to go through all this
just to return a 0 when there is an error. I am wondering if I could
create a dynamic range that would help (though I need to repeat this
formula many times and the range setup might get frustrating).

Thanks.

v/r

Paul Z.
 
T

Tyro

Switch to Excel 2007 and use your formula only once. In Excel 2007
=IFERROR(formula,error_condition).

Tyro
 
P

pzeitlin

Switch to Excel 2007 and use your formula only once. In Excel 2007
=IFERROR(formula,error_condition).

Tyro









- Show quoted text -
Click to expand...

haha...corporate computer so I can't do that yet. BUT..it's coming and
wondered (beyond the autrocious new GUI) what benefit belied Excel
2007. Thanks for that insight Tyro!
 
T

Tyro

I came from Excel 2003 to 2007. Excel 2007 is much, much easier to use than
previous versions. The ribbon puts everything in front of you. At most, you
have to go down only one level to get to the detail. It is very intuitive.
Much better than drill-down menus.

Tyro

Switch to Excel 2007 and use your formula only once. In Excel 2007
=IFERROR(formula,error_condition).

Tyro









- Show quoted text -
Click to expand...

haha...corporate computer so I can't do that yet. BUT..it's coming and
wondered (beyond the autrocious new GUI) what benefit belied Excel
2007. Thanks for that insight Tyro!
 
S

Susan

one idea - you use this range: Cost!$I$427:$HI$434
several times.
it would save a lot of characters if you defined the range in the
workbook with a name that has less characters.

like
(HLOOKUP(reporting_period,RangeA

that might help some.
ps - i DO NOT have excel 07, & Tyro does, but personally from what
i've read in the excel newsgroups, it comes with a lot of problems.
:) just my $0.02
hth
susan
 
I

ilia

You can create a custom worksheet function to have the same
functionality, without Excel 2007. However, it won't be as quick - in
fact, possibly intolerably slow. But, something along these lines:

Public Function IfIsError(myTest As Variant, defaultValue As Variant)
If IsError(myTest) Then
IfIsError = defaultValue
Else
IfIsError = myTest
End If
End Function

Usage would be like this, in your case, and assuming you put this
function in your personal macros workbook

=PERSONAL.XLS!IfIsError(IF(G36="","",(1-(IF(Inputs!B7=FC,
(HLOOKUP(reporting_period,Cost!$I$427:$HI$434,(MATCH(DeloitteMetrics!
G36,Cost!$H$427:$H$434,0)),TRUE)),(SUMPRODUCT((Cost!$C$8:$C
$57=second_function)*(net_revenue_range))))+(IF(G36=prime_function,
(SUM('D Metrics'!$C$28:$C$31)),0)))/(SUMPRODUCT((Cost!$C$8:$C
$57=second_function)*(gross_revenue_range))))),0)

Hope that helps.
 
Top