I've been creating new workbooks after being out of excel for many years. These new workbooks will grow so I'm curious about my dynamic named ranges. I've always used offset, but have learned index may be a better solution due to volatility. Can someone explain why one function is better than the other in this situation?
Hi Russ,
Welcome to our forum!
Evaluation for volatile functions is triggered more frequently than non-volatile functions. As a result, your workbook can become unnecessarily bogged down in calcs, making it slow.
See this post for a more thorough explanation.
INDEX is not a volatile function, so it's deemed preferable for dynamic ranges over OFFSET. However, if you only have a few OFFSET functions, you won't notice any impact on calculation times.
Mynda
If you're using syntax like $A$1:INDEX(...), with the INDEX on one or both sides of the colon, then it is semi-volatile which means that it will recalculate when the workbook is opened, but not every time there is a calculation.
