Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    -- or 1* or ?? (any)

    I'm curious about the pros and cons (and personal philosophy) of the various ways to resolve to 1s and 0s the TRUE and FALSE results from a logical test. I've used both -- and 1* and suspect that in general 1* is probably marginally slower because of the multiplication versus the double negation (what about 0+). I'm wondering what the faster(est) method would be and the "more readable" for someone possibly supporting the code. Too bad their isn't a meaningful function to do this or that Excel simply doesn't just resolve the TRUE and FALSE to a 1 and 0.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: -- or 1* or ?? (any)

    I did a test with 7*65536 cells containing TRUE/FALSE values. There was only a very small difference between using -- or 1* or 0+ to convert to 1/0. There *is* a function you can use: the N function, but it was about 40 to 50% slower than the other methods. Still, unless you have a workbook with a very large number of TRUE/FALSE values, you won't notice the difference.

    Note: as soon as you involve a TRUE/FALSE value in a calculation, it will be interpreted as 1/0, for example, if A1 contains TRUE, the formula =A1+3 will return 4. No need to use =--A1+3 or something like that.

  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: -- or 1* or ?? (any)

    I don't know what is fastest, but if you want readability, I think there is an indirect correlation between readability and speed. If you want it readable, use the IF statements, intermediate columns, etc as they are generally better understood than the megaformulas, array formulas, and the boolean logic.

    I don't understand why you would always want them "resolved" to 0/1 and not want to use TRUE or FALSE by itself.

    XL resolves it when you use it for arithmatic and it resolves it to TRUE/FALSE if you use a number in an IF.

    Steve

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: -- or 1* or ?? (any)

    You want them resolved to a 1 or 0 so that, for example, the SUM function in an array formula would work (see the recent post from Hans to someone regarding this).
    Hans: Thanks for the testing. I always felt that the N() function was probably slow and you confirmed it.

  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: -- or 1* or ?? (any)

    <P ID="edit" class=small>(Edited by sdckapr on 10-Mar-06 11:16. OOPS Added PS)</P>But a SUM only sums numbers. It is a special function which ignores non-numeric values (true/false are non-numeric)

    Your suggestion is to not have a Boolean value at all, which I do not think makes any sense. One way to do this is to change the transition option:
    Tools - options - Transition(tab)
    Check the "Transition formula evaluation"

    This will not only resolve, True/False to 1/0, but also resolve Text to 0.

    Steve

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> OOPS this does not resolve the problem with the array formula, it onlys seems the final value not the intermdiates are transformed. The array formulas still need to be converted before summing.

Posting Permissions

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