Forum

Notifications
Clear all

index vs offset in dynamic names ranges

3 Posts
3 Users
0 Reactions
289 Views
(@rlee)
Posts: 1
New Member
Topic starter
 

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?

 
Posted : 06/08/2022 9:57 pm
(@mynda)
Posts: 4762
Member Admin
 

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

 
Posted : 09/08/2022 7:07 am
(@debaser)
Posts: 838
Member Moderator
 

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.

 
Posted : 09/08/2022 8:48 am
Share: