From the Not Just Numbers blog:
Let me start with an apology. My current workload has meant that my posts have been a lot less frequent in recent weeks. This may continue for a while, but I will still be aiming to post at least once a month during this busy spell.
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”.
You might also be interested in
Replies (0)
Please login or register to join the discussion.
There are currently no replies, be the first to post a reply.