Eng-Tips is the largest engineering community on the Internet
Intelligent Work Forums for Engineering Professionals
-
Congratulations waross on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
OFFSET vs INDEX 1
- Thread starter AELLC
- Start date
See:
In summary, offset is more versatile, but is volatile, and hence slower.
I always use Index when I can, just out of habit, but it looks like that's the best thing to do.
Doug Jenkins
Interactive Design Services
In summary, offset is more versatile, but is volatile, and hence slower.
I always use Index when I can, just out of habit, but it looks like that's the best thing to do.
Doug Jenkins
Interactive Design Services
- Thread starter
- #3
Doug,
1) What did you mean by offset is more versatile?
2) My largest Excel is about 4.7 MB, and uses a lot of OFFSET, but recalc seems instantaneous (I have a very fast computer)
3) See attached .xls - it is just a example sort of thing to compare different methods - the part I still don't get at all, is why are there 2 types of INDEX, namely Reference and Array, and why does the Reference version (cell D31) returns #REF! (??)
1) What did you mean by offset is more versatile?
2) My largest Excel is about 4.7 MB, and uses a lot of OFFSET, but recalc seems instantaneous (I have a very fast computer)
3) See attached .xls - it is just a example sort of thing to compare different methods - the part I still don't get at all, is why are there 2 types of INDEX, namely Reference and Array, and why does the Reference version (cell D31) returns #REF! (??)
-
1
- #4
AELLC,
1. By more flexible I meant that the cell indexed in the Index function has to be within the specified range, whereas with Offset the function works from the top left cell of the given range, so you don't need to know the size of the data range. You could enter just the single top left cell, and Offset will return the value of any cell within the spreadsheet, including those with negative offset values. The other thing is that Index will return a single cell, or a single column or row from the specified table range, whereas offset can return a range of any specified size. The latter feature I use quite a lot in VBA, but rarely on the spreadsheet.
2. The main difference in speed is that Offset is volatile, which means that it will recalc every time anything changes anywhere in the spreadsheet, whereas index will only result in a recalc if a value is changed within the specified Index range. If your recalc speed is near enough to instantaneous that you don't notice it, then there is no reason not to use Offset.
3. I had to look up the Reference version. I think the given "help" is awful (even by MS help standards). By the "reference" format they seem to mean that you can list multiple ranges, inside brackets, and select one of those ranges with the third index value. The index values still have to be inside the specified range, so if you enter a single cell as the "reference", the index values would have to be 1,1,1 (or just 1 will work). If you have a single range specified then the "reference" version is exactly the same as the "array" version (unless I'm missing something).
It seems that the ranges all have to be on the same sheet. That's pretty poor, Quattro Pro and Lotus 123 had true 3D functionality more than 20 years ago!
Doug Jenkins
Interactive Design Services
1. By more flexible I meant that the cell indexed in the Index function has to be within the specified range, whereas with Offset the function works from the top left cell of the given range, so you don't need to know the size of the data range. You could enter just the single top left cell, and Offset will return the value of any cell within the spreadsheet, including those with negative offset values. The other thing is that Index will return a single cell, or a single column or row from the specified table range, whereas offset can return a range of any specified size. The latter feature I use quite a lot in VBA, but rarely on the spreadsheet.
2. The main difference in speed is that Offset is volatile, which means that it will recalc every time anything changes anywhere in the spreadsheet, whereas index will only result in a recalc if a value is changed within the specified Index range. If your recalc speed is near enough to instantaneous that you don't notice it, then there is no reason not to use Offset.
3. I had to look up the Reference version. I think the given "help" is awful (even by MS help standards). By the "reference" format they seem to mean that you can list multiple ranges, inside brackets, and select one of those ranges with the third index value. The index values still have to be inside the specified range, so if you enter a single cell as the "reference", the index values would have to be 1,1,1 (or just 1 will work). If you have a single range specified then the "reference" version is exactly the same as the "array" version (unless I'm missing something).
It seems that the ranges all have to be on the same sheet. That's pretty poor, Quattro Pro and Lotus 123 had true 3D functionality more than 20 years ago!
Doug Jenkins
Interactive Design Services
- Thread starter
- #5
- Status
