Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
ExcelMonkey
 
Posts: n/a
Default 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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Gord Dibben
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Service work order schoolgeek63 Charts and Charting in Excel 1 January 7th 05 09:07 PM
decimal point override does not work Sam Brauen Excel Discussion (Misc queries) 0 January 6th 05 06:29 PM
Lookup Doesn't Work spalmarez Excel Worksheet Functions 3 November 9th 04 09:23 PM
The colors should work properly in Shared Excel Workbooks. [email protected] Excel Worksheet Functions 1 November 8th 04 11:55 PM
How do I ensure dates inputted are during the work week? Jim Johnson Excel Worksheet Functions 3 October 29th 04 08:25 AM


All times are GMT +1. The time now is 04:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"