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

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.

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

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

Tyro

wrote in message
...
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.

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

On Jan 9, 11:30*am, "Tyro" wrote:
Switch to Excel 2007 and use your formula only once. In Excel 2007
=IFERROR(formula,error_condition).

Tyro

wrote in message

...



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.- Hide quoted text -

- Show quoted text -

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!

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

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

wrote in message
...
On Jan 9, 11:30 am, "Tyro" wrote:
Switch to Excel 2007 and use your formula only once. In Excel 2007
=IFERROR(formula,error_condition).

Tyro

wrote in message

...



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.- Hide quoted text -

- Show quoted text -

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!

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

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




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.

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

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.