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.