OFFSET is a flexible function.
It can be used to point to a variable top left cell,
and then point to a range that has a variable shape.
The problem with OFFSET is that it is volatile.
Volatile functions calculate at every calculation.
Instead of OFFSET, you can use INDEX(): or :INDEX()
When INDEX is next to a :, it will return a cell address instead of the value at that cell.
INDEX is not volatile
It can be used to point to a variable top left cell,
and then point to a range that has a variable shape.
The problem with OFFSET is that it is volatile.
Volatile functions calculate at every calculation.
Instead of OFFSET, you can use INDEX(): or :INDEX()
When INDEX is next to a :, it will return a cell address instead of the value at that cell.
INDEX is not volatile
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2048 - :INDEX Will Replace a Volatile OFFSET!
Hey, I'm podcasting all of my tips from this book, click that “i” in the top-right hand corner to get to the playlist!
Alright, OFFSET is an amazing function!
OFFSET allows us to specify a top left corner cell, and then use variables to define from there how many rows down, how many rows over, and then define a shape, alright.
So here, if I want to add up, or do an average of, let's say 3/4, this formula right here will take a look at the formula.
The OFFSET says we're starting from B2, starting from Q1, we're going down 0, over 0, the shape will be 1 row tall, and it will be H1, in other words 3 cells wide, alright.
So in this case all we're really doing is changing how many cells we're adding up, we're always starting back in B2, or B3 or B4 as I copy down, and then it decides how many cells wide.
Alright, OFFSET is this cool thing, it does all kinds of amazing functions, but here's the hassle, it is volatile!
Which means that even if something's not in the calculation chain, Excel, is going to take the time to recalculate it, which is going to slow things down, alright so.
This amazing version of INDEX, right, normally if I would ask for the INDEX of these 4 cells ,3 , it's going to return the number 1403.
However, when I put a colon either before or after the INDEX, something very different happens, we'll take a look at this formula here.
So index of the 4 cells, which one do I want, I want the 3rd one, but you see there's a : right there.
So we're always going to go from B2:E2, and I'll show you if we go to Formulas, Evaluate Formula, alright, so right here it's going to calculate the number 3.
And here, the INDEX with the : next to it, instead of telling us 1403, which is how INDEX normally would calculate, it's going to return $D2, and then the AVERAGE will do the average of those 3.
Absolutely amazing, the way that this works, and the added benefit, it is not volatile, alright, and this can even be used to replace an incredibly complex OFFSET.
So here with this OFFSET we're based on these values.
If I choose Q2 and Central, alright, these formulas are using MATCH and COUNTIF to figure out how many rows down, how many columns over, how tall, how wide.
And then the OFFSET here is using all of those values to figure out the median.
We'll just do a test to make sure that it is working, so =MEDIAN of that blue range over there, better be 71, alright, and we'll choose something else here, Q3 and West, so.
Press F2, I'm just going to drag this over and resize it to rewrite that formula, press Enter, and it's working with the OFFSET.
Well here, using an INDEX, I actually have a colon in the middle with an INDEX on the left side and an INDEX on the right side, watch this thing get calculated in Evaluate Formula.
So it starts out, will Evaluate, Evaluate, and right here that INDEX is about to turn it into a cell address.
So H16 is the 1st, West, Q3, and over on the right side will evaluate, couple more, and then right her it changes to I20.
So the cool, cool non-volatile to replace an OFFSET using the INDEX function.
Alright, this tip and many more in this book, click that “i” in the top-right hand corner to buy the book.
Alright recap: OFFSET, awesome, flexible function, once you master, you can do all kinds of things.
Point to a variable top-left cell, point to a range that has a variable shape, but it's volatile, and so they calculate at every calculation.
Excel usually does a smart calculation, or it recalculates the cells that need to be calculated, but with OFFSET it will always get calculated.
Instead of OFFSET you can use INDEX: or :INDEX or even INDEX:INDEX, anytime INDEX has a colon next to it, it will return a cell address instead of the value of that cell.
And the benefit is INDEX is not volatile!
OK, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Hey, I'm podcasting all of my tips from this book, click that “i” in the top-right hand corner to get to the playlist!
Alright, OFFSET is an amazing function!
OFFSET allows us to specify a top left corner cell, and then use variables to define from there how many rows down, how many rows over, and then define a shape, alright.
So here, if I want to add up, or do an average of, let's say 3/4, this formula right here will take a look at the formula.
The OFFSET says we're starting from B2, starting from Q1, we're going down 0, over 0, the shape will be 1 row tall, and it will be H1, in other words 3 cells wide, alright.
So in this case all we're really doing is changing how many cells we're adding up, we're always starting back in B2, or B3 or B4 as I copy down, and then it decides how many cells wide.
Alright, OFFSET is this cool thing, it does all kinds of amazing functions, but here's the hassle, it is volatile!
Which means that even if something's not in the calculation chain, Excel, is going to take the time to recalculate it, which is going to slow things down, alright so.
This amazing version of INDEX, right, normally if I would ask for the INDEX of these 4 cells ,3 , it's going to return the number 1403.
However, when I put a colon either before or after the INDEX, something very different happens, we'll take a look at this formula here.
So index of the 4 cells, which one do I want, I want the 3rd one, but you see there's a : right there.
So we're always going to go from B2:E2, and I'll show you if we go to Formulas, Evaluate Formula, alright, so right here it's going to calculate the number 3.
And here, the INDEX with the : next to it, instead of telling us 1403, which is how INDEX normally would calculate, it's going to return $D2, and then the AVERAGE will do the average of those 3.
Absolutely amazing, the way that this works, and the added benefit, it is not volatile, alright, and this can even be used to replace an incredibly complex OFFSET.
So here with this OFFSET we're based on these values.
If I choose Q2 and Central, alright, these formulas are using MATCH and COUNTIF to figure out how many rows down, how many columns over, how tall, how wide.
And then the OFFSET here is using all of those values to figure out the median.
We'll just do a test to make sure that it is working, so =MEDIAN of that blue range over there, better be 71, alright, and we'll choose something else here, Q3 and West, so.
Press F2, I'm just going to drag this over and resize it to rewrite that formula, press Enter, and it's working with the OFFSET.
Well here, using an INDEX, I actually have a colon in the middle with an INDEX on the left side and an INDEX on the right side, watch this thing get calculated in Evaluate Formula.
So it starts out, will Evaluate, Evaluate, and right here that INDEX is about to turn it into a cell address.
So H16 is the 1st, West, Q3, and over on the right side will evaluate, couple more, and then right her it changes to I20.
So the cool, cool non-volatile to replace an OFFSET using the INDEX function.
Alright, this tip and many more in this book, click that “i” in the top-right hand corner to buy the book.
Alright recap: OFFSET, awesome, flexible function, once you master, you can do all kinds of things.
Point to a variable top-left cell, point to a range that has a variable shape, but it's volatile, and so they calculate at every calculation.
Excel usually does a smart calculation, or it recalculates the cells that need to be calculated, but with OFFSET it will always get calculated.
Instead of OFFSET you can use INDEX: or :INDEX or even INDEX:INDEX, anytime INDEX has a colon next to it, it will return a cell address instead of the value of that cell.
And the benefit is INDEX is not volatile!
OK, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
