3 ms·
The dependency graph is why you should always prefer INDEX over OFFSET, unless you really can't avoid it: https://docs.microsoft.com/en-us/office/client-develo
by rahimnathwani 6y ago
The dependency graph is why you should always prefer INDEX over OFFSET, unless you really can't avoid it:
https://docs.microsoft.com/en-us/office/client-developer/excel/excel-recalculation#dependence-dirty-cells-and-recalculated-cells https://docs.microsoft.com/en-us/office/client-developer/exc...
Every instance of OFFSET in your sheet is recalculated after each cell change. I learnt that the hard way.
- sheetjs 6y agoArticle doesn't explain the issue, but OFFSET lets you access cells that aren't referenced in the formula. For example, OFFSET(B2,1,1,1,1) is a live reference to cell C3, which means you can use functions like COLUMN to investigate the range. C3 shows up nowhere in the formula, so there's no non-volatile way to implement it. The first argument to INDEX is a "sqref" (cell, range, or set of ranges) and INDEX will error if you try to reference a cell outside of the sqref, so use of INDEX doesn't break the obvious dependency structure
- cm2187 6y agoor worse, INDIRECT, which allows you to arbitrarily refer to any cell anywhere with a string.
- FabHK 6y ago"Volatile functions are always dirty."