| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Non cumbersome way to hide and protect a column? | Excel Worksheet Functions | |||
| nested IF AND too cumbersome | Excel Worksheet Functions | |||
| If alternative | Excel Worksheet Functions | |||
| cumbersome sheets on Word | Excel Worksheet Functions | |||
| ISERROR | Excel Worksheet Functions | |||
Linear Mode
