Microsoft Office Tutorials and References
In Depth Information
Creating Megaformulas
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
Method
Recalculation Time (Seconds)
File Size
Intermediate formulas
5.8
12.60MB
Megaformula
3.9
2.95MB
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.
 
Search JabSto ::




Custom Search