Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Cleaning up Data (XL-2003)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Loungers

    I need your help in checking up on this:

    1) I got a huge amount of data from a very old, DOS days, source to clean up and produce a MS-Excel workbook. All went well for a while. Then
    2) I wrote some formulas to help clean up some more. After the clean up, I Saved Values.
    3) Way down in the worksheet I found some #VALUE!.
    4) I wrote this formula: =IF(OR(ISERROR(D12),D12<>""),0,1) and the portion D12<>"" still returns #VALUE! thus the formula will return #VALUE!.

    My questions, and I have been away from MS-Excel for a while now:

    1) When you save as values, is #VALUE! considered an error value or a text value, or ??? value?
    2) I thought that the Or section is handled from left to right and since the ISERROR(D12) returns True, then it would skip the come next. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    3) Any better way to re-write this formula to work with error values and text values together? <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

    Thanks a Million <img src=/S/money.gif border=0 alt=money width=17 height=15>

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Cleaning up Data (XL-2003)

    How about:
    =IF(ISERROR(D12),0,IF(D12<>"",0,1))

    [With your code, if D12 is an error, then the "ISERROR(D12)" will be TRUE, The second part of the OR (D12<>"") becomes an error so you are trying to OR a TRUE and an error which results in an error...]

    This checks for an error, if an error report a zero. If not an error, check if D12 is null, if it is give a zero, if it is not, give a 1.

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Cleaning up Data (XL-2003)

    Forgot your other question.

    When you paste special values an error, it remains an error.

    Steve

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Cleaning up Data (XL-2003)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Steve

    <font color=blue>
    [With your code, if D12 is an error, then the "ISERROR(D12)" will be TRUE, The second part...</font color=blue>
    Why is the 2nd part being evaluated if the OR needs only one "branch" to return TRUE for the OR to return TRUE? Am I missing something here? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    <font color=blue>you are trying to OR a TRUE and an error which results in an error.</font color=blue>
    Again my question. I was under the impression that using OR its either argument returning TRUE will be enough. I guess there is something special with errors or I misread the OR statement?! Would it not be redundant if both arguments are evaluated before the OR expression is evaluated?

    Thanks a Million again, I will use your version of the formula.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Cleaning up Data (XL-2003)

    My point was (and still is <img src=/S/smile.gif border=0 alt=smile width=15 height=15>) that your impression is incorrect. Assume you have your formula:
    =IF(OR(ISERROR(D12),D12<>""),0,1)

    And presume that D12 has the #value error in it.

    Excel will be evaluating:
    =IF(OR(ISERROR(#value),#value<>""),0,1)

    Which becomes:
    =IF(OR(TRUE,#value),0,1)

    Which is:
    =IF(#value,0,1)

    which evaluates as #value error ...

    The OR can not be evaluated unless both items are evaluated. the second part of the or can not be evaluated since it is an error.

    Steve

Posting Permissions

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