Use A2:INDEX() as a Non-Volatile OFFSET

January 06, 2020 Mr King Excel Tips,

Use A2:INDEX() as a Non-Volatile OFFSET

There is a flexible function called OFFSET. It can point to a different-sized range that is calculated on-the-fly. In the image below, if someone changes the # Qtrs dropdown in H1 from 3 to 4, the fourth argument of OFFSET will make sure that the range expands to include four columns.

There are names in A2:A7. Four quarters of sales stretch across B:E for each name. Over in H1, you enter the number of quarters you want included. The Average formula in F2 uses =AVERAGE(OFFSET(B2,0,0,1,$H$1)).

Spreadsheet gurus hate OFFSET because it is a volatile function. If you go to a completely unrelated cell and enter a number, all of the OFFSET functions will calculate—even if that cell has nothing to do with H1 or B2. Most of the time, Excel is very careful to only spend time calculating the cells that need to calculate. But once you introduce OFFSET, all of the OFFSET cells, plus everything downline from the OFFSET, starts calculating after every change in the worksheet.

In the formula below, there is a colon before the INDEX function. Normally, the INDEX function shown below would return the 1403 from cell D2. But when you put a colon on either side of the INDEX function, it starts returning the cell address D2 instead of the contents of D2. It is wild that this works.

Replace the OFFSET function in F2 with =AVERAGE(B2:INDEX(B2:E2,$H$1)).

Why does this matter? INDEX is not volatile. You get all of the flexible goodness of OFFSET without the time-sucking recalculations over and over.

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1