Poll on - Lots of simple formulas vs fewer but more complex formulas

Some valuable reading here: http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

It is an interesting question. A few of my thoughts:

1) All else being equal (same number of total operations, etc.), I expect that the difference in computation time between having the calculation spread over several cells vs. being crammed into megaformulas is probably small. I am reminded of this xkcd comic: http://xkcd.com/1445/ All else being equal, I expect one will spend a lot more time deciding which approach is more efficient than will ever be recovered.

Along these same lines, I am reminded of a comment my mentor put into one of his code blocks. In commenting on why he chose a certain approach, he said that he recognized that this approach can slow the program down, but he felt that it was easier for the programmer to code, debug, and change. Easier to code and debug made the code more reliable/accurate, and made it faster and easier to code and change. In other words, he was willing to make the computer work harder so that it was easier for him. In this respect, since I find it easier to code multiple simple formulas into multiple cells than to try and code and debug a single megaformula, I prefer to break my computations up into multiple steps.

2) Of course, everything is not always equal. I have often noticed something that Olly's link points out. When we use "megaformulas" and array formulas, we increase the possibility of "duplicated effort" -- each copy of a megaformula ends up repeating the same "lookup" or other computation over and over. I recall a discussion here between another user and me (I think it has been lost in a crash) where he compared a few different mega - array formulas performance over a large database. I recognized the duplicated effort in his formulas, and pulled that part out into a helper column. Doing so reduced what was a 30-40 second computation down to 1-2 seconds. IMO, smaller formulas make it easier to recognize and separate out duplicated effort, and thus improve spreadsheet performance.

There is calculation timer code at this link:

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

You can run some tests and see for yourself which is faster.

I've seen mega-formulas posted here that wrap to five..six.. lines. What happens when it's been a year since you've seen it and now have to modify it for some reason? You may also move on to greener pastures leaving mega-forumulas to some one who is seeing them for the first time, and some one wants mods now...

I have always taken the approach, that where possbble/practical, I try and use a few smaller formulas, rather than 1 huge, impressive-looking formula. Small formulas are far easier to edit/adjust/understand, and more importantly, trouble-shoot.

If this might result in a slight performance loss, then maybe that is something to consider