Forum Discussion
Can INDIRECT be used to dynamically alter the second range reference in a Named Range
In any event, I can see why OFFSET might be slow in Excel--there's simply, potentially, a lot to resolve, so if it's being used a lot, repeatedly, or in multiple spots in a given workbook, it could indeed slow things down.
In the use that I'm making of it--simply to define the range to which a Name is being applied, it gets used once upon opening a sheet, and never again. Simply used to take care of a data table that can change its dimensions from time to time, and do so without requiring manual redefinition.
Volatile function doesn't mean that function is slow itself. Volatile function means the function which is recalculating every time when Excel recalculates something else. Obvious example of volatile function is NOW() - it's extremely fast itself, but if we have lot of them in different cells and formulas entire their recalculation on every click could significantly slow overall Excel performance.
My view - yes, in general it's better to avoid volatile functions. On the other hand that's not must. With proper design of Excel workbook volatile functions could be effective enough, especially if take into account efforts on maintenance - they don't cost few saved milliseconds in calculations.
More about that in two good articles
Excel performance: Tips for optimizing performance obstructions
Excel performance: Improving calculation performance
And citation from the latest:
-
Avoid volatile functions such as INDIRECT and OFFSET where you can, unless they are significantly more efficient than the alternatives. (Well-designed use of OFFSET is often fast.)
- Well explained, as always! Nonetheless, non-volatile alternatives are preferable, as can be gleaned from the cited article.
-
- I convinced Kevin Lehrbass to shift from OFFSET to INDEX here:
https://www.myspreadsheetlab.com/dynamic-ranges-using-index-function/I agree that if volatile functions are avoidable then we should avoid them 🙂 Twifoo
Often the non volatile formula alternative has more complex syntax but for serious development we should take the time to avoid volatile functions whenever possible.
Obviously, as SergeiBaklan mentioned above, there are times when NOW() (volatile) is unavoidable.
Thanks to Twifoo for various non volatile alternative solutions on my blog this year!
