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.