Is a single formula better than intermediate calculations?

I've used Excel for a while, but I am new to forums.

What's been a real eye-opener for me is that the solutions posted are not the way I would have approached the problem in the past.

I come from a (procedural) programming background, where we perform operations step by step. I tend to use intermediate calculation columns, with only 1 or 2 calculations per formula, or, failing that, use a UDF. I find this easier to debug and maintain.

The solutions I see tend to give the result in one formula. This reminds me of the functional progamming languages I used years ago.

I'd like your opinions on what is best-practice, or common practice, in relation to constructing formulas, so that I can improve my development.

IMO it all boils down to efficiency...

If a formula can be condensed into one efficient formula then it makes sense to do so.

However, it is often the case that an "elegant" approach to a complex problem will be inefficient and "helper" / intermediate calculation approaches are the better approach long term for the sake of usability.

The above is perhaps most prevalent with multi conditional calculations where people tend to be over reliant on Arrays / SUMPRODUCT ... when such formulas are either a) over used or b) used sparingly but say in a Volatile manner they will quickly impact overall performance and ultimately the viability of the model in which they reside.

As a thread subject - it's pretty broad ... and it all boils down to context, the particulars in each scenario - what holds true in one model might not in another.

If you're interested in optimisation I'd suggest having a read through of Charles Williams' site - he's considered by many to be one of the real experts on this subject.

http://www.decisionmodels.com/calcsecrets.htm

He's also written a white paper for MS re: XL2007

http://msdn.microsoft.com/en-us/library/aa730921.aspx


All that being said there's little value to be had in an elegant model if those using & maintaining it do not understand it... things going wrong is a fact of life and it's important that there are people around able to fix it...

Well, it depends, really. If there is an expensive lookup that takes a lot of processing power, it pays to have that formula in a helper column and refer other formulae to that result instead of repeating the same lookup over and over again in a single, long formula.

In other cases, you won't achieve any benefits from splitting your calculation into separate cells, so you may as well do it all in one cell.

It often also depends on the proficiency of the user and the level of formula nesting they can stomach. Long formulae tend to be unwieldy and hard to troubleshoot, especially if you have not written them yourself or if you have written them a while ago forgotten what you were after.

But with tools like Evaluate Formula, it's not too hard to figure out what a formula does.

Speed of calculation would be the most important factor, especially with large datasets, and maintainability the other.

There are almost always alternatives to a solution that suit one person better than another.