IF Formulas

[sumsum]

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",????

[Ken Puls]

Hi sumsum, and welcome to the forum.

Try this: =IF(A1<1000,0,A1)

[sumsum]

Thanks Ken.

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

[Bob Phillips]

Just because I like no-IF formulas

=A1*(A1>=1000)

[JeffreyWeir]

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

=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.

[Ken Puls]

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.)

, and (2) it makes other excel nerds excited.

Now that's an EXCELLENT reason though!

[Bob Phillips]

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.

[JeffreyWeir]

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.

[Ken Puls]

That obvious? I wouldn't have expected that. Will keep in mind though.

[Jon von der Heyden]

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.