StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>As for the second part, if you use ordinary non-volatile functions then multiple simple formulas could be better for two reasons:</p> <ol> <li>On simple recalculations (without rebuilding dependency trees) Excel will calculate only the parts that actually changed, e.g. in your single step example if value in <code>A1</code> changes then Excel will have to recalculate the expression in the parentheses <code>(C1 * D1 / B1)</code> even if values of <code>C1, D1, B1</code> are unchanged. When you replace that part with a reference to F1, the value of F1 will not be recalculated if only A1 changes its value.</li> <li>Multiple simple formulas could be better calculated in parallel if you have multiple cores.</li> </ol> <p>another usefull link in addition to MSDN: <a href="http://www.decisionmodels.com/calcsecretsc.htm" rel="nofollow">http://www.decisionmodels.com/calcsecretsc.htm</a></p> <p>Volatile functions are evil in very large workbooks, especially OFFSET and INDIRECT. They all are recalculated every time anything changes in a file, and they are always calculated in a single thread. Any cell that depends on a cell with a volatile function becomes volatile as well, because all dependencies have to be recalculated every time a volatile function is recalculated. This <strong>viral volatility</strong> in a big file could seriously damage performance. Using many simple formulas helps in this case as well, since many dependencies could remain non-volatile. </p> <p>From the link above: </p> <blockquote> <p>Some Excel features do not use multithreaded calculation, for example:</p> <p>Data table calculation (but structured references to tables do use MTC). User-defined functions (but XLL functions can be multithread-enabled). XLM functions. INDIRECT, CELL functions that use either the format2 or address options. GETPIVOTDATA and other functions referring to PivotTables or cubes. Range.Calculate and Range.CalculateRowMajorOrder. Cells in circular reference loops.</p> </blockquote> <p>Once upon a time I inherited a big file that took 30 min to recalculate on a dedicated fast machine and that was due to crazy usage of OFFSETS to access data from a big sheet. Just by moving calculation logic from Excel to Access and importing results via a pivot table I reduced total calculation time to several seconds!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload