Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Oakland, CA, USA
    Posts
    161
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ISERROR Function (Excel 97/2000)

    Hello,

    I've been looking at all the functions and wanted to find out if someone can give me a reason/example why I would use the ISERROR function.

    Thank you very much,
    Tira

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ISERROR Function (Excel 97/2000)

    Tira, it is used to check for errors. It allows you to do alternative processing when there is an error condition. For examples, check out that, and <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=68627&page=&v iew=&sb=&o=&vc=1#Post68627>the other</A> threads. Hope this helps! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: ISERROR Function (Excel 97/2000)

    The ISERROR() function can be used to see if a particular expression returns an Error, such as #DIV/0!. Suppose you have 2 values in A1 and B1 and in C1 you want to the result of A1/B1, but if B1 = 0 that would return an error. So in C1 you could use <pre> =IF(ISERROR(A1/B1),"",A1/B1)</pre>

    What that does is return a blank if the division produces an eorror, otherwise the actual division.

    Andrew C

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    A new function: IFERROR()

    Frequently when using ISERROR, my formula reads

    =IF(ISERROR(GreatBigLongFormula,"",GreatBigLongFor mula)

    It always seemed to me that a new function, say IFERROR (patent pending) would be a good idea. The above formula would then become:

    =IFERROR(GreatBigLongFormula,"")

    - meaning, of course, that you wouldn't have to repeat the GreatBigLongFormula.
    I once briefly tried to create a custom function to do same, but gave up.
    I wonder if Excel 2002 has such a function?

    P.S. How do you make your posted formulas appear in Courier font?

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: A new function: IFERROR()

    << How do you make your posted formulas appear in Courier font? >>

    When you use the PRE tags (see Tag Panel) special formatting is applied which uses the Courier font. This formatting is intended for code listings, and as it does not word wrap is not really suitable for general use.

    Good idea about the IFERROR function, but a UDF might be too cumbersome and difficult to implement, especially if you want to include all possible built in functions.

    As far as your GreatBigLongFormula is concerned, when you enter the first occurrence highlight it and press Ctrl-C (or Ctrl-Insert) and then place the cursor where the second occurrence is required and press Ctrl-V. It might save some typing on very long formulae.

    Andrew C

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: A new function: IFERROR()

    Thanks. I do use the Ctrl-C and Ctrl-V to save typing - it was more a question of readability.

    <pre>This is a test...</pre>


  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: A new function: IFERROR()

    Rory,

    Tried you code with this simple situation : Cells A1 to A1 contain numbers except for one which contains 1/0, (#DIV0!).

    =ErrHandle(SUM(A1:A10)) returns #VALUE!, whereas the idea is that it should return a null or blank.

    I think there are some functions that do not work with the evaluate method, but am not 100% sure, but SUM() is not one of them.

    Andrew C

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: A new function: IFERROR()

    Hi Andrew,
    I've edited my function as I decided it was terrible too - you had to pass the formula to it as a string (which I think is why you were getting the #VALUE error) and this meant that you couldn't copy it down or across cells effectively (all the references remained the same.) The revised version I think is much better but please feel free to break it! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: A new function: IFERROR()

    Rory,

    Your version 2 passes my previous test, and also a VLOOKUP. Nice one. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    How about a second optional argument that is returned if an error does result. Omitting the argument returns a blank cell ! <pre>Function ErrHandle(strFormula, Optional strRet As String) As Variant
    Application.Volatile
    If IsError(strFormula) Then
    ErrHandle = strRet
    Else
    ErrHandle = strFormula
    End If
    End Function</pre>

    Andrew

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: A new function: IFERROR()

    I like it - you get to choose your error message... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: A new function: IFERROR()

    Now I just need a way to pass the <img src=/S/confused.gif border=0 alt=confused width=15 height=20> smilie as an argument to return on error..... <img src=/S/smile.gif border=0 alt=smile width=15 height=15> (probably better received than some of the semi-swearwords I use currently)
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: A new function: IFERROR()

    Thanks! Definitely swiped, this will go in my bag with the following tweak; the ability to set what you want to return if the formula in fact evaluates to an error:

    Function IFERROR(strFormula, Optional SetTo As Variant) As Variant
    Application.Volatile
    If IsError(strFormula) Then
    IFERROR = SetTo
    Else
    IFERROR = strFormula
    End If
    End Function

    So you can use a string or NA() as "SetTo" arguments, with default return as zero. (As you can see, I preferred the "IFERROR" name. I was noodling around with this with CVErr and getting nowhere.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: A new function: IFERROR()

    This any good?
    <pre>Function IfError(varFormula As Variant, _
    Optional varErrMsg As Variant = vbNullString) As Variant
    Application.Volatile
    If IsError(varFormula) Then
    IfError = varErrMsg
    Else
    IfError = varFormula
    End If
    End Function
    </pre>


    Edited by me as the first version was very cumbersome. Later edited again to add optional error message after collaboration with Andrew, Geoff and John - Rory
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Oakland, CA, USA
    Posts
    161
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ISERROR Function (Excel 97/2000)

    Thanks for your info Andrew. Tira

  15. #15
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Oakland, CA, USA
    Posts
    161
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ISERROR Function (Excel 97/2000)

    Thanks for your info, Sam. Yet, why use the formula because one will see that there is an error anyway, so why would one want it to say "True" or "False"? <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    Tira

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •