Log In
Register
Home
New Member
August 6, 2022
Offline
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?
July 16, 2010
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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.
1 Guest(s)
