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! :lol:

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

[JeffreyWeir]

There's also some discussion about IF in the comments at http://www.dailydoseofexcel.com/archives/2011/04/04/how-to-make-excel-crash/

[Bob Phillips]

I disagree entirely. To my mind, as someone who rarely owns the spreadsheet but either creates spreadsheets that others run with or having to pick up the work of others, the most important thing to me is maintainability. To that end, obfuscation of formulas is a total no-no. If your spreadsheet performs so badly that you need to shave off micro-seconds by making the formula incomprehensible, you are on the wrong road.

[JeffreyWeir]

I'm a bit lost about what you're disagreeing with, Bob...I thought you liked no-if formulas?

Any formula can be fully documented just like any good model can. I've seen plenty of incomprehensible models that use nothing more complicated than a VLOOKUP.

To my mind, one bonus of trying to push the envelope in regards to constructing very clever formulas - albeit formulas that less advanced users might not understand without documentation - is that you really start to understand what is possible by stringing formulas - excel's equivalent of DNA base pairs - together.

[JeffreyWeir]

Whoops, pushed send before I'd finished.

Take an offset-based dynamic range formula...this is well over the heads of most users - to them it would probably fit their definition of 'obfuscation'. Go back to the first time it was 'invented'...it was probably considered bad form because of this. But it is very well documented on the internet. And it's very useful. Now it's commonplace among your 'intermediate' users.

Now, take an index-based dynamic range formula. Much less used, much less documented, generally much less understood out there in user land purely because they learned the OFFSET method. But often it's much much better because it's non-volatile. It's not intrinsically any harder to understand than OFFSET, it's just not as familiar to most people.

[Bob Phillips]

I do like them, just as I like SUMPRODUCT and array formulas, but only from an aesthetic view. I would argue strongly against using such things in a production spreadsheet to any large degree, maybe at all, because too many people don't understand them, and they become practically unauditable. And I feel exactly the same about OFFSET, if you have the need for such techniques to any degree it is usually better to do it via VBA IMO, code is far easier to audit. Wrap obfuscating formulae in obfuscating range names and you have a problem just waiting to arise (oh, and OFFSET is often in a range name and used in CF, but CF is implicitly volatile, so even using INDEX would garner no saving there). You need to know about these things and their impact on performance, but to me design is far more important than fancy formulae, KISS is always a good maxim.

[Jon von der Heyden]

Yup, I'm going to agree with Bob here (as usual). Whilst I think micro-optimisation is very important, I think one must always consider whether or not it is practical. Practical in the sense that it needs to be easy to understand. Not just for others but for you too. I've written heaps of ugly formula and often confused myself when looking back at them. KISS for sure, but always keep optimisation in mind at the same time. Simple things like keeping a sorted list and then use binary search versions of lookup functions etc... :)

[Ken Puls]

Sumsum, I hope you got the answer you needed from this, and please accept my apologies for the hijack of the thread. Just for reference, if you're not wanting to follow the discussion (which I have a feeling may go on a bit more), you can unsubscribe from it by using the Thread Tools menu at the top of the thread. There's an unsubscribe there. :)

Now, re the modelling/formula discussion...

I've recently built a massive model that uses over 50,000 formulas. The model is quite dynamic, allowing use to change all kinds of inputs. Because of this, a great many of those formulas were IF statements, some used in conditional formats, and all of the reporting sheets are built using a huge amount of Index/Match/Match combinations that change depending on the choices made by users. There is virtually no VBA in the file at all.

I've got no concerns with performance in this file whatsoever. It calculates just as quickly to the human eye as if there was only one worksheet with 100 formulas on it.

So this is where my comments came from. I've never noticed that extensive use of IF functions causes a big delay... maybe it's just me, but that one really hasn't been on my radar. VLOOKUP vs INDEX/MATCH? Absolutely. That one can be a real killer.

The big dilemma to me when building this stuff is trying to strike the balance between:

Getting the project done on time
Using less formulas to avoid someone overwriting one accidentally
Making the file maintainable by someone else (this is probably the hardest)
Keeping calc speed up
Reigning in my desire to build something just to satisfy the "geek" factor
I tried to put those in order of importance too, but again it's difficult.

I guess what I'm trying to say is that striking the difference between shaving off a few nano-seconds that an Excel pro might notice vs making the file maintainable for someone else... I'd tend to lean to the latter... most of the time.

[Bob Phillips]

I've recently built a massive model that uses over 50,000 formulas. The model is quite dynamic, allowing use to change all kinds of inputs. Because of this, a great many of those formulas were IF statements, some used in conditional formats, and all of the reporting sheets are built using a huge amount of Index/Match/Match combinations that change depending on the choices made by users. There is virtually no VBA in the file at all.

That is because the recalculation engine in Excel is very smart, it maintains a dependency tree, and only cells within the dependency tree recalculate (I know you know this Ken). As such, a well designed spreadsheet that doesn't make all the formulae co-dependent can be fast even with lots of formulae, as you are seeing. Which reinforces my statement that if you need to shave off micro-seconds, you are on the wrong road; design is the key. As an aside, I saw an article recently about the guy who came up with the calculation algorithm, unfortunately I cannot remember where, and he is a civil liberties lawyer now if my memory serves me well.

[Ken Puls]

That is because the recalculation engine in Excel is very smart, it maintains a dependency tree, and only cells within the dependency tree recalculate...

Right, but what I'm saying is that my model is built like a tree. And if you change an input at the very root, which affects everything up the calc chain through the upmost branches it still calcs fast. I'd have to go and audit it, but I'll bet that I have some input cells that have 1000 dependant formulas...

[goneps]

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

[Ken Puls]

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.

[Bob Phillips]

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

:)

[goneps]

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

[Bob Phillips]

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.

[goneps]

Thanks, Bob—useful to know. R.