Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 25

Thread: IF Formulas

  1. #1

    IF Formulas



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

    Having a little trouble with an IF formula.

    Column A1 = $1050

    In Column A2 I want it to say, if A1<1000 = 0, if A1>1000 then enter the value from A1
    So in this example the answere in A2 would be $1050

    IF(A1<1000,"0",????

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,189
    Articles
    57
    Blog Entries
    14
    Hi sumsum, and welcome to the forum.

    Try this: =IF(A1<1000,0,A1)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

  3. #3
    Thanks Ken.

    So happy to find this forum, what a great resource.

  4. #4
    Just because I like no-IF formulas

    =A1*(A1>=1000)

  5. #5
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Sumsum: you should also check out http://www.excelhero.com/blog/2010/01/i-heart-if.html for some more background on IF as well as some alternatives. Such as
    Code:
    =A1*(A1>=1000)
    which basically says
    multiply A1 by one if A1 >= 1000, otherwise multiply A1 by zero
    . The part (A1>=1000) is an example of boolean logic: it returns "True" if true and "FALSE" if false. When Multiplying a "TRUE" or "FALSE" with something (in this case, A1), Excel turns a TRUE into 1 and a FALSE into zero.

    The 2 reasons you might want to do this is that (1) using boolean logic is much less processer intensive than using IF, and (2) it makes other excel nerds excited.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,189
    Articles
    57
    Blog Entries
    14
    Quote Originally Posted by JeffreyWeir
    The 2 reasons you might want to do this is that (1) using boolean logic is much less processer intensive than using IF
    I doubt most users would notice the effects of this unless they had thousands of these calls in their spreadsheets. (And possibly tens of thousands.)

    Quote Originally Posted by JefferyWeir
    , and (2) it makes other excel nerds excited.
    Now that's an EXCELLENT reason though!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

  7. #7
    Quote Originally Posted by Ken Puls View Post
    I doubt most users would notice the effects of this unless they had thousands of these calls in their spreadsheets. (And possibly tens of thousands.)
    And if they do, it will be so slow they won't notice the difference anyway.

  8. #8
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    I agree gents that on this occaison you might not notice, but good practice is good practice, and the sooner one learns about it and gets into the habit, the better. If you know that such an approach exists, and routinely use it, then when you need it, it's second nature. Just like putting your most prevelent cases at the top of a Select Case routine, or using Else If.

    I wish I had learnt this years ago myself...I was doing analysis of half hourly data from electritity meters, with each site's data using up 17520 rows. That was one unresponsive spreadsheet after I'd added a few other sites and some formulas running down the entire length using the IF statement.

    More recently, I was doing some dynamic charting that updated every second in excel 2007. It had less than 100 IF statements in it, but it really struggled to get everything plotted before the vba routine caused everything to recalculate for the next set of data. After I got rid of the IFs it worked perfectly.

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,189
    Articles
    57
    Blog Entries
    14
    That obvious? I wouldn't have expected that. Will keep in mind though.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

  10. #10
    Acolyte Jon von der Heyden's Avatar
    Join Date
    Mar 2011
    Location
    Stellenbosch,South Africa
    Posts
    24
    Articles
    0
    I think micro-optimisation in complex large models is an abslute must. The IF avoidance is but one example but being aware of better methods and applying them throughout the model is what really makes a difference. Plus, understanding boolean logic and avoiding function calls like this, when applied in array formulae, really can make a noticeable difference.

Page 1 of 3 1 2 3 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
  •