Cool INDEX Function Trick

What say you want to find the percentage increase or decrease for the last value in a list compared to the minimum?

Sure you could do it the manual way, but I’m going to show you a way you can update the calculation automatically each time a new value is added to the bottom using the INDEX Function.

Taking this list we can use the formula below to find the last value in column B and divide it by the minimum value in the list:

=INDEX(B:B,COUNT(B:B)+2)/MIN(B:B)-1

How The Formula Above Works

Alternatively we could find the percentage change from the starting value of 51 compared to the last value in the list like this:

=INDEX(B:B,COUNTA(B:B)+2)/$B$4-1

How The Formula Above Works

As usual there are many ways you could achieve these calculations but one of the benefits of using the INDEX function is that it is non-volatile.

Non-what?

That's non-volatile. A volatile function is recalculated every time any data in your workbook changes.

This can render your workbook very slow to recalculate and best practice is to avoid volatile functions where possible.

Some common volatile functions are OFFSET, INDIRECT, ROWS, COLUMNS, NOW, TODAY and CELL.

If your workbook is large I recommend you use volatile functions sparingly…. unless you have a super computer (and no one else needs to open your workbook), in which case feel free to fling volatile functions around as you wish!

For more tutorials like this sign up to our free weekly Excel tips & tricks newsletter below, or click the Facebook 'like' button and get updates to this blog instantly.