Bill Explains it like a 'Swiss Army Knife' - which is a bit...odd... but it really does make sense. Ron is trying to get a better understanding of the use of OFFSET...but sometimes there are better means to an end. Follow along with Bill Today, in Episode #1637, as he explains the functionality of the 'Swiss Army Knife', the OFFSET Function and where each applies.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by: Easy-XL.
Learn Excel for MrExcel podcast, Episode 1637 -- Sum Which Month with OFFSET.
Hey welcome back to the MrExcel netcast, I'm Bill Jelen.
I was talking to Ron, Ron is trying to pick up OFFSET, he says --you know this is so confusing, and he was asking me about using OFFSET to get a variable length range and he says -- well okay, what about the second and third arguments?
I said hang on Ron.
I was just in Switzerland and they have all these different Swiss Army knives, and a Swiss Army knife can do a dozen different things but here's the thing with a Swiss Army knife -- you are never going to use this blade, and this blade at the same time, right?
You're going to use a Swiss Army knife for one thing, have one blade open and use it that way.
If you try and use a Swiss Army knife with three blades open you're going to get hurt and OFFSET is the same way; all right we're either going to use the second and third arguments of OFFSET, or the fourth and fifth arguments of OFFSET, it's rare to use all of them at once -- so don't think about all of those other tools you have, we're thinking about just the issue today.
So here I have six departments, 12 months going across and someone needs to be able to come here and enter a date so: 3/31, and we want to pull up the total of that particular month.
All right so I don't know in advance what date they're going to put in; depending on what date they put in I have to grab one of these 12.
Could I solve this with an IF?
Oh geez, I guess I could but it would be something really ugly like: =IF(MONTH(F1)=1,SUM(B6:B11),IF(MONTH(F1)=2,SUM.
This right, you could just imagine how bad this is going to be that we're going to have 12 IFs – this means it would not even work back in Excel 2003; and while it would work here in Excel 2007 and newer, it's not something we want to do so the OFFSET will allow us to do this.
With the OFFSET I know that we're always going to start from this cell A6, now we're going to go to down zero rows and then we're going to go over some number of columns -- how many columns over?
It's the month of F1, so right now March 31st, the month of that is three.
From here we go over one, two, three, and then -- how large of a range do we want to add up?
Well, in this case always six rows, one column.
All right so I know, I know I'm just going to build, not a formula here just; I know that we're going to start from A6, we're going to go down zero rows, we're going to figure out the month -- it's always going to be six rows tall, one column wide, I know that.
All right so the variability in this, the tool that we're using here in this particular version of OFFSET is, there's going to be a different number of columns over, everything else is stuff I know, right?
I could just build SUM function if it wasn't for the fact that I don't know how many columns over to go.
All right so let's take a look at the formula that I have here, we're always starting from A6 we're going zero rows down, you know this is not even being used, it's one of those blades of the Swiss Army knife that we're not opening for this particular technique -- it's going to be six rows tall, it's going to be one column wide and then, how many columns over from here do I want to use?
I want to use the month of F1, so right now the month of F1 is 3 -- let's just use Mike Girvin’s trick there, I'll select that press F9, it’s 3, Control-Z.
If I come here and change this to 12/31, then the month of F1 is going to be 12.
All right, why did I choose to start from A6?
Because I'm lazy; if I would have started from B6, I would have had to take the month and subtract 1 and I didn't want to have to subtract 1, that's why I started from here.
What if we didn't have column A?
Well yet then I'd have to start from here, and use the month of F1, minus 1 because I have to go over one less.
Could I have started from here?
Ah yeah but geez that would make my head hurt -- we would be using negative columns and it wouldn't be the negative the month, they would have to be negative 13 minus the month or something like that; and you could start from wherever you want but, start from the easiest spot; month is going to give us the numbers 1 through 12 so that's where we go.
OFFSET, it's confusing if you try and use all the blades at once, it can be used for so many different things -- it's usually we’re just going to have one bit of that offset that it's going to change and, you know, that's the one that you're focusing on.
I want to thank Ron for sending in that question; I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
Learn Excel for MrExcel podcast, Episode 1637 -- Sum Which Month with OFFSET.
Hey welcome back to the MrExcel netcast, I'm Bill Jelen.
I was talking to Ron, Ron is trying to pick up OFFSET, he says --you know this is so confusing, and he was asking me about using OFFSET to get a variable length range and he says -- well okay, what about the second and third arguments?
I said hang on Ron.
I was just in Switzerland and they have all these different Swiss Army knives, and a Swiss Army knife can do a dozen different things but here's the thing with a Swiss Army knife -- you are never going to use this blade, and this blade at the same time, right?
You're going to use a Swiss Army knife for one thing, have one blade open and use it that way.
If you try and use a Swiss Army knife with three blades open you're going to get hurt and OFFSET is the same way; all right we're either going to use the second and third arguments of OFFSET, or the fourth and fifth arguments of OFFSET, it's rare to use all of them at once -- so don't think about all of those other tools you have, we're thinking about just the issue today.
So here I have six departments, 12 months going across and someone needs to be able to come here and enter a date so: 3/31, and we want to pull up the total of that particular month.
All right so I don't know in advance what date they're going to put in; depending on what date they put in I have to grab one of these 12.
Could I solve this with an IF?
Oh geez, I guess I could but it would be something really ugly like: =IF(MONTH(F1)=1,SUM(B6:B11),IF(MONTH(F1)=2,SUM.
This right, you could just imagine how bad this is going to be that we're going to have 12 IFs – this means it would not even work back in Excel 2003; and while it would work here in Excel 2007 and newer, it's not something we want to do so the OFFSET will allow us to do this.
With the OFFSET I know that we're always going to start from this cell A6, now we're going to go to down zero rows and then we're going to go over some number of columns -- how many columns over?
It's the month of F1, so right now March 31st, the month of that is three.
From here we go over one, two, three, and then -- how large of a range do we want to add up?
Well, in this case always six rows, one column.
All right so I know, I know I'm just going to build, not a formula here just; I know that we're going to start from A6, we're going to go down zero rows, we're going to figure out the month -- it's always going to be six rows tall, one column wide, I know that.
All right so the variability in this, the tool that we're using here in this particular version of OFFSET is, there's going to be a different number of columns over, everything else is stuff I know, right?
I could just build SUM function if it wasn't for the fact that I don't know how many columns over to go.
All right so let's take a look at the formula that I have here, we're always starting from A6 we're going zero rows down, you know this is not even being used, it's one of those blades of the Swiss Army knife that we're not opening for this particular technique -- it's going to be six rows tall, it's going to be one column wide and then, how many columns over from here do I want to use?
I want to use the month of F1, so right now the month of F1 is 3 -- let's just use Mike Girvin’s trick there, I'll select that press F9, it’s 3, Control-Z.
If I come here and change this to 12/31, then the month of F1 is going to be 12.
All right, why did I choose to start from A6?
Because I'm lazy; if I would have started from B6, I would have had to take the month and subtract 1 and I didn't want to have to subtract 1, that's why I started from here.
What if we didn't have column A?
Well yet then I'd have to start from here, and use the month of F1, minus 1 because I have to go over one less.
Could I have started from here?
Ah yeah but geez that would make my head hurt -- we would be using negative columns and it wouldn't be the negative the month, they would have to be negative 13 minus the month or something like that; and you could start from wherever you want but, start from the easiest spot; month is going to give us the numbers 1 through 12 so that's where we go.
OFFSET, it's confusing if you try and use all the blades at once, it can be used for so many different things -- it's usually we’re just going to have one bit of that offset that it's going to change and, you know, that's the one that you're focusing on.
I want to thank Ron for sending in that question; I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
