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,(MAT CH(DeloitteMetrics!
G36,Cost!$H$427:$H$434,0)),TRUE)),(SUMPRODUCT((Cos t!$C$8:$C
$57=second_function)*(net_revenue_range))))+(IF(G3 6=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.


On Jan 9, 10:24 am, wrote:
Gang,

I have the following iserror formula:

=IF(ISERROR(IF(G36="","",(1-(IF(Inputs!B7=FC,
(HLOOKUP(reporting_period,Cost!$I$427:$HI$434,(MAT CH('Deloitte
Metrics'!G36,Cost!$H$427:$H$434,0)),TRUE)),(SUMPRO DUCT((Cost!$C$8:$C
$57=second_function)*(net_revenue_range))))+(IF(G3 6=prime_function,
(SUM('D Metrics'!$C$28:$C$31)),0)))/(SUMPRODUCT((Cost!$C$8:$C
$57=second_function)*(gross_revenue_range))))))=TR UE,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)),(SUMPRO DUCT((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.