Page 3 of 3 FirstFirst 1 2 3
Results 21 to 25 of 25

Thread: IF Formulas

  1. #21
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi goneps,

    That's a perfectly acceptable use of IF statements to me. I don't believe there is any way around them for that purpose.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  2. #22
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,663
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by goneps View Post
    I plead guilty to the charge of using a lot of IF statements, but more often than not they are to forestall multiple #DIV/0! errors when the precedents are not filled in. For instance:

    =IF(AND(A1=0,C1=0),0,A1+C1)

    So how can the boolean method be used in those circumstances? Blowed if if can work it out.

    Richard
    I first structured it to a boolean form and, even though I feel it is pointless and wrong, I came up with

    =--(NOT(AND(A1=0,C1=0)))*(A1+C1)

    But then I looked at your formula and came up with

    =A1+C1


  3. #23
    Seeker goneps's Avatar
    Join Date
    Dec 2012
    Location
    Auckland, New Zealand
    Posts
    13
    Articles
    0
    OK, Bob—I oversimplified it. I should have used =A1*C1 as an example, since most of my formulas involve multiplication or division, so we're back to the problem of thousands of #DIV/0! errors unless we implement some means of taming them.

    Nonetheless, thanks for showing that it IS possible, even though it's not pretty. Because I use this form of IF statement so extensively I've been surprised it hasn't received so much as a mention in any of the articles I've read on the subject.

    Would you explain the function of the two hyphens "--" after "="?

    Richard

  4. #24
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,663
    Articles
    0
    Excel Version
    O365
    The -- is a method of coercing an expression that resolves to TRUE or FALSE to 1 or 0 instead, so as to get a numeric result.

    In this case it is actually redundant, because the * operator will also have the effect of coercing TRUE or FALSE to 1 or 0.

  5. #25
    Seeker goneps's Avatar
    Join Date
    Dec 2012
    Location
    Auckland, New Zealand
    Posts
    13
    Articles
    0
    Thanks, Bob—useful to know. R.

Page 3 of 3 FirstFirst 1 2 3

Posting Permissions

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