As for the second part, if you use the usual non-volatile functions, then a few simple formulas may be better for two reasons:
- With simple recounts (without restoring dependency trees), Excel will only calculate those parts that have actually been changed, for example. in the one-step example, if the value in
A1 changes, Excel will have to recalculate the expression in parentheses (C1 * D1 / B1) , even if the values of C1, D1, B1 do not change. When you replace this part with a reference to F1, the value of F1 will not be recalculated unless A1 changes its value. - A few simple formulas can be better calculated in parallel if you have multiple cores.
another useful link in addition to MSDN: http://www.decisionmodels.com/calcsecretsc.htm
Volatile functions are evil in very large books, especially OFFSET and INDIRECT. All of them are recounted every time something changes in the file, and they are always calculated in one thread. Any cell that depends on a cell with a volatile function also becomes volatile, since all dependencies must be recalculated every time a mutable function is recalculated. This viral volatility in a large file can seriously affect performance. Using many simple formulas also helps in this case, since many dependencies can remain unstable.
From the above link:
Some Excel functions do not use multi-threaded calculations, for example:
Calculation of a data table (but structured table references use MTC). Custom functions (but XLL functions may be multi-threaded). Xlm. INDIRECT CELLS that use either format2 or address parameters. GETPIVOTDATA and other functions related to pivot tables or cubes. Range.Calculate and Range.CalculateRowMajorOrder. Cells in circular support cycles.
Once I inherited a large file, which in 30 minutes counted on a dedicated fast machine, and this was due to the crazy use of OFFSET to access data from a large sheet. By simply moving the calculation logic from Excel to Access and importing the results using a pivot table, I reduced the total calculation time to a few seconds!