In this post I want to show you how to do something I have shown before using OFFSET, but this time using the more efficient INDEX.
Let's look at the example of a 12 month budget spreadsheet with the monthly sales figures in cells B9 to M9 (month 1 in B9, Month 2 in C9, etc.). And let's say that the current month number is entered in cell B6.
Using OFFSET we could return the current month's sales using:
=OFFSET($B9,0,$B$6-1)
and the cumulative sales using:
=SUM(OFFSET($B9,0,0,1,$B$6))
See this earlier post, if you don't understand why.
The problem with OFFSET though, is that it is what is known as a "volatile" function. This means that it always has to be recalculated when any cell in the spreadsheet changes - as it does not specify a range that it is dependent on, therefore does not know whether a change might affect its result.
INDEX however looks at a defined range, making it significantly more efficient, and in its simplest form can be used to replace both of these examples of OFFSET.
For a range the width of a single cell (as in our example), INDEX only needs two arguments to return the current month's sales:
=INDEX($B9:$M9,$B$6)
This returns the value of the cell in position B6 in the range B9:M9.
If $B$6 is 3, this will return the value in cell D9, being the third cell in the range B9:M9.
To do the cumulative calculation, we can use the same INDEX function to return the end of a SUM range, while fixing the start:
=SUM($B9:INDEX($B9:$M9,$B$6))
Again, if B6 contains 3, then this returns the sum of the range B9:D9.
In both cases, these will only recalculate if a cell in the range B9:M9 is edited. In a complex spreadsheet with many calculations, this can make a huge difference to calculation times.
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".
