Microsoft Office Tutorials and References
In Depth Information
Because a megaformula is so complex, you may think that using one slows down recalculation.
Actually, that’s not the case. As a test, I created a workbook that used the megaformula 175,000
times. Then I created another workbook that used six intermediate formulas to compute the
175,000 results. I compared the results in terms of calculation time and file size; see Table 3-6.
Table 3-6: Comparing Intermediate Formulas and Megaformula
Recalculation Time (Seconds)
The actual results will vary significantly, depending on system speed, amount of memory
installed, and the actual formula.
The VBA function was much slower — I abandoned the timed test after five minutes. This is fairly
typical of VBA functions; they are always slower than built-in Excel functions.