IF Formulas

[JeffreyWeir]

There's also some discussion about IF in the comments at http://www.dailydoseofexcel.com/arch...e-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