Home |
Search |
Today's Posts |
#1
|
|||
|
|||
ISERROR Functin to Much Work!
I often use the ISERROR function to wrap around a formual that may have a an error in it. I use if nested in an If stmt to say if the formual generates an error, then produce a zero value otherwise, do I want I want it to. For example: IF(ISERROR(A1/B1),0,A1/B1) Problem is that you have to type in the fist part of the formual A1/B1 again at the end of the IF stmt. This is really a pain if the condition is ver large: =IF(ISERROR(IF(Inputs!$G$550 = "Denominator",(Statements!H101+Statements!H102 )/(Statements!H101+Statements!H102+Statements!H106), 1)),0,IF(Inputs!$G$550 = "Denominator",(Statements!H101+Statements!H102 )/(Statements!H101+Statements!H102+Statements!H106), 1)) Is there a formual or userdefined function that acts like an iserror wrapped in an If stm that will allow me to not repeat the formula? It would look like this: Iserror2(condition,result if condition is false) or Iserror2(A1/B1,0) or Iserror2(IF(Inputs!$G$550 = "Denominator",(Statements!H101+Statements!H102 )/(Statements!H101+Statements!H102+Statements!H106), 1)),0) Does anybody know if this exists or hoe to build this in VBA? thnx -- ExcelMonkey ------------------------------------------------------------------------ ExcelMonkey's Profile: http://www.excelforum.com/member.php...fo&userid=5221 View this thread: http://www.excelforum.com/showthread...hreadid=277709 |
#2
|
|||
|
|||
Hi
this ISERROR2 kind of function does not exist directly in Excel. You can of course create it. for storing a variable have a look at http://makeashorterlink.com/?J32E23767 (option 2+3 - the latter one reposted below) ----- Public Function V(Optional vrnt As Variant) As Variant ' ' Stephen Dunn ' 2002-09-12 ' Static vrntV As Variant If Not IsMissing(vrnt) Then vrntV = vrnt V = vrntV End Function ------ use it in your formula as follows: IF(ISERROR(V(your_formula)),"Error",V()) -- Regards Frank Kabel Frankfurt, Germany "ExcelMonkey" schrieb im Newsbeitrag ... I often use the ISERROR function to wrap around a formual that may have a an error in it. I use if nested in an If stmt to say if the formual generates an error, then produce a zero value otherwise, do I want I want it to. For example: IF(ISERROR(A1/B1),0,A1/B1) Problem is that you have to type in the fist part of the formual A1/B1 again at the end of the IF stmt. This is really a pain if the condition is ver large: =IF(ISERROR(IF(Inputs!$G$550 = "Denominator",(Statements!H101+Statements!H102 )/(Statements!H101+Statem ents!H102+Statements!H106),1)),0,IF(Inputs!$G$550 = "Denominator",(Statements!H101+Statements!H102 )/(Statements!H101+Statem ents!H102+Statements!H106),1)) Is there a formual or userdefined function that acts like an iserror wrapped in an If stm that will allow me to not repeat the formula? It would look like this: Iserror2(condition,result if condition is false) or Iserror2(A1/B1,0) or Iserror2(IF(Inputs!$G$550 = "Denominator",(Statements!H101+Statements!H102 )/(Statements!H101+Statem ents!H102+Statements!H106),1)),0) Does anybody know if this exists or hoe to build this in VBA? thnx -- ExcelMonkey --------------------------------------------------------------------- --- ExcelMonkey's Profile: http://www.excelforum.com/member.php...fo&userid=5221 View this thread: http://www.excelforum.com/showthread...hreadid=277709 |
#3
|
|||
|
|||
Tom Ogilvy presented this UDF yesterday.......
Function Noerorvlookup(Lookup As Variant, Rng As Range, Col As Long) Dim res as Variant res = Application.VLookup(Lookup, Rng, Col, False) If IsError(res) Then Noerorvlookup = 0 Else Noerorvlookup = res End If End Function OR a macro to add the ISERROR to existing formulas....... Sub ErrorTrapAdd() Dim myStr As String Dim cel As Range For Each cel In Selection If cel.HasFormula = True Then If Not cel.Formula Like "=IF(ISERROR*" Then myStr = Right(cel.Formula, Len(cel.Formula) - 1) cel.Value = "=IF(ISERROR(" & myStr & "),""""," & myStr & ")" End If End If Next End Sub Gord Dibben Excel MVP On Fri, 12 Nov 2004 08:29:16 -0600, ExcelMonkey wrote: I often use the ISERROR function to wrap around a formual that may have a an error in it. I use if nested in an If stmt to say if the formual generates an error, then produce a zero value otherwise, do I want I want it to. For example: IF(ISERROR(A1/B1),0,A1/B1) Problem is that you have to type in the fist part of the formual A1/B1 again at the end of the IF stmt. This is really a pain if the condition is ver large: =IF(ISERROR(IF(Inputs!$G$550 = "Denominator",(Statements!H101+Statements!H102 )/(Statements!H101+Statements!H102+Statements!H106), 1)),0,IF(Inputs!$G$550 = "Denominator",(Statements!H101+Statements!H102 )/(Statements!H101+Statements!H102+Statements!H106), 1)) Is there a formual or userdefined function that acts like an iserror wrapped in an If stm that will allow me to not repeat the formula? It would look like this: Iserror2(condition,result if condition is false) or Iserror2(A1/B1,0) or Iserror2(IF(Inputs!$G$550 = "Denominator",(Statements!H101+Statements!H102 )/(Statements!H101+Statements!H102+Statements!H106), 1)),0) Does anybody know if this exists or hoe to build this in VBA? thnx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Service work order | Charts and Charting in Excel | |||
decimal point override does not work | Excel Discussion (Misc queries) | |||
Lookup Doesn't Work | Excel Worksheet Functions | |||
The colors should work properly in Shared Excel Workbooks. | Excel Worksheet Functions | |||
How do I ensure dates inputted are during the work week? | Excel Worksheet Functions |