Stacy has to adjust formulas every year so they average the last five years of figures. This episode offers several techniques to make this easier. The Laminated Formula Tip card mentioned in the episode is for sale at: Formula Tips from MrExcel (Laminated Card)
Transcript of the video:
MrExcel podcast sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1811.
Adjust Formulas Every Year.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Another question from the Fort Myer seminar, this one from Stacey.
Stacey has a complicated spreadsheet lots of different rows, but blanks in between explanatory notes section headings and so on and she has a formula that is averaging the most recent five years and she actually has all the other years back here they're hidden though she doesn't want it to lead them in case you ever need to go back and check something and so, as you can imagine every year Stacey comes in and adds a new column here with 2014 and you know then we have data that has to be included in that average.
So, we want it now, average D, E, F, G, H, but not I.
Okay so, a boy have a lot of different ways to go with this one of them well obviously just come in edit the first formula and then once we've done that if although the formulas in this column are the same we can copy the formula and then I'll hold down the shift key page down.
So, we select all the formulas control+G or F5, click Special choose formulas, click OK.
Now, we've selected only the formula cellls and I can Alt+E+S for Paste Special and formulas, click OK and we've now, converted all of those formulas to the new cells.
So, that's one way to go.
I'm going to undo all the way back through that including inserting the, the new column.
Another way to go I would be to take all of the cells in this column and we're going to control+H, We're gonna change every occurrence of equal to the carrot equals, Replace all and I will actually see what the formulas are we can insert a new column 2014 put some data here and then go back to our column O, control+H again and this time we're going to go backwards.
So, we're going to do the carrot equals just to our regular equals and Replace All and the result is going to be that those formulas are still pointing to D3 to H3 every single time.
All right, another way to go I'm gonna do undo, undo, undo, undo all the way back undo.
Okay would be to use the offset function.
So, let's just start with offset.
Offset we're start from C3 comma how many rows down from there no rows down, how many columns over from there, always one column to the right and then point to range that's one row tall by five rows wide.
All right! So, we then have to wrap that in our Average function.
All right and again kind of a tedious process the first time to paste these throughout if all of the formulas over here are exactly the same and control+G, Special, Formulas, click OK and Alt+E+S+F, Enter.
So, we have now, offset functions everywhere.
The beautiful thing about offset. Well, it is a volatile function it takes a little bit longer to calculate.
I think Stacey sets it at 275 rows.
So, it's not going to be that much longer to calculate.
The beautiful thing here is when we insert a new column to the right of column C.
So, insert column and we'll put 2014 here now, I'm just going to throw a very small number in like 1 and what we're gonna see is all of these numbers here then should become much smaller 1, control+enter there we go.
Yeah so, the offset function is now, getting the first five cells to the right of column D and we don't have to change the function each year again and again, again.
None of the hokey carrot equals or you know it'll just, it'll just work from here on out.
Let's do a quick little test 1, 2, 3, 4, 5, those five cells the average down here is 1212 which is the average we get there looks like it's working.
All right! Well hey, I wanna thank Stacy for asking that question down in Fort Myers.
I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey for more great formula tips check out my formula tips laminated tip card it's a card you can keep it right there by your computer all kinds of great functions like OFFSET, VLOOKUP if you want more, just to 95 at the MrExcel store.
Learn Excel from MrExcel podcast episode 1811.
Adjust Formulas Every Year.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Another question from the Fort Myer seminar, this one from Stacey.
Stacey has a complicated spreadsheet lots of different rows, but blanks in between explanatory notes section headings and so on and she has a formula that is averaging the most recent five years and she actually has all the other years back here they're hidden though she doesn't want it to lead them in case you ever need to go back and check something and so, as you can imagine every year Stacey comes in and adds a new column here with 2014 and you know then we have data that has to be included in that average.
So, we want it now, average D, E, F, G, H, but not I.
Okay so, a boy have a lot of different ways to go with this one of them well obviously just come in edit the first formula and then once we've done that if although the formulas in this column are the same we can copy the formula and then I'll hold down the shift key page down.
So, we select all the formulas control+G or F5, click Special choose formulas, click OK.
Now, we've selected only the formula cellls and I can Alt+E+S for Paste Special and formulas, click OK and we've now, converted all of those formulas to the new cells.
So, that's one way to go.
I'm going to undo all the way back through that including inserting the, the new column.
Another way to go I would be to take all of the cells in this column and we're going to control+H, We're gonna change every occurrence of equal to the carrot equals, Replace all and I will actually see what the formulas are we can insert a new column 2014 put some data here and then go back to our column O, control+H again and this time we're going to go backwards.
So, we're going to do the carrot equals just to our regular equals and Replace All and the result is going to be that those formulas are still pointing to D3 to H3 every single time.
All right, another way to go I'm gonna do undo, undo, undo, undo all the way back undo.
Okay would be to use the offset function.
So, let's just start with offset.
Offset we're start from C3 comma how many rows down from there no rows down, how many columns over from there, always one column to the right and then point to range that's one row tall by five rows wide.
All right! So, we then have to wrap that in our Average function.
All right and again kind of a tedious process the first time to paste these throughout if all of the formulas over here are exactly the same and control+G, Special, Formulas, click OK and Alt+E+S+F, Enter.
So, we have now, offset functions everywhere.
The beautiful thing about offset. Well, it is a volatile function it takes a little bit longer to calculate.
I think Stacey sets it at 275 rows.
So, it's not going to be that much longer to calculate.
The beautiful thing here is when we insert a new column to the right of column C.
So, insert column and we'll put 2014 here now, I'm just going to throw a very small number in like 1 and what we're gonna see is all of these numbers here then should become much smaller 1, control+enter there we go.
Yeah so, the offset function is now, getting the first five cells to the right of column D and we don't have to change the function each year again and again, again.
None of the hokey carrot equals or you know it'll just, it'll just work from here on out.
Let's do a quick little test 1, 2, 3, 4, 5, those five cells the average down here is 1212 which is the average we get there looks like it's working.
All right! Well hey, I wanna thank Stacy for asking that question down in Fort Myers.
I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey for more great formula tips check out my formula tips laminated tip card it's a card you can keep it right there by your computer all kinds of great functions like OFFSET, VLOOKUP if you want more, just to 95 at the MrExcel store.
