Performance of Excel formulas - are they smart enough to skip and remember?

[Jaymond Flurrie]

How does the Excel formulas work under the hood? I mean, I can write a long IF-formula, which would logically skip most of the formula, but does Excel solve it all anyway "just in case"?

Also, if I use the same complicated formula multiple times in a formula, does Excel understand that it already calculated the result once and thus dramatically reduce the calculation time or does it just calculate everything anyway?

 

[thisoldman]

Excel does use short-circuit evaluation of IF()s. But ...

...Excel creates a decision tree for calculations—"if Cell A1 changes, then recalculate Cells B2, B5, C3, C6, etc." Nested IF()s can slow down overall Excel performance, as can array formulas, and conditional formatting. I try to minimize IF()s by using Lookup tables, or the Boolean operators AND() and OR(), and using Excel's builtin coercion of TRUE and FALSE to the numbers 1 and 0.

Instead of:
=IF(A1=1, "Low", IF(A1=2, "Middle", IF(A1=3, "High","")))

I will use:
=REPT("Low", A1=1)&REPT("Middle", A1=2)&REPT("High", A1=3)
which does the same thing.

Old article, but still useful: http://blog.excelhero.com/2010/01/21/i_heart_if/

Excel does not cache results for repeated formulas. You're better off performing the calculation once and then referring to the cell that contains the result in subsequent formulas.

For performance optimization, see: https://msdn.microsoft.com/en-us/vb...-tips-for-optimizing-performance-obstructions

To read more than you will ever retain about Excel's calculation performance: http://www.decisionmodels.com/calcsecrets.htm

 

[thisoldman]

Two more articles about alternatives to nested IFs():

http://spreadsheetpage.com/index.php/tip/alternatives_to_nested_if_functions/
https://exceljet.net/nested-ifs