Today's question: You have to rent rail tank car #123 for a 3 month period. The daily rate changes twice ; once in May, and once in June. Given the start date, end date, and the rate changes, how can you calculated the total rental charge or average per day? Bill shows how he has been reading MIke's Ctrl+Shift+Enter book and creates an array formula. Mike makes the formula better.
Links:
Mike's Ctrl+Shift+Enter book: Ctrl + Shift + Enter
Bill's May 9 seminar in Tampa:
www.eventbrite.com
Mike's appearance on ExcelTV:
Links:
Mike's Ctrl+Shift+Enter book: Ctrl + Shift + Enter
Bill's May 9 seminar in Tampa:
Bill "MrExcel" Jelen at Spring IMA FL Council Seminar - 8 CPE
Not to Be Missed! Bill "MrExcel" Jelen 8 CPE Seminar at IMA Florida Council. Partner group: IIA West Coast Chapter * VALUE -includes 8 CPE(TB), two(2) books to take home and continental & lunch **For Regular and Heavy Users of Excel - CFO's, Analysts, Accountants, Marketers, Finance...
www.eventbrite.com
Mike's appearance on ExcelTV:
Transcript of the video:
It must be time for another dueling Excel podcast.
I'm Bill Jelen from MrExcel, be joined by Mike Girvin from Excel Is Fun.
This is our episode 158.
Working on the Railroad.
Hey, Mike, all right! Welcome back time for another dueling Excel podcast.
Great appearance on Excel TV, those guys are a lot of fun, aren't they.
I hope to see you next Friday May 9th, across the country from you about as far as you can get from Washington down, Tampa, Florida.
I'll be doing our Excel live hope to see you down there.
I'm so excited you sent this question in and said you're not sure, what the answer is going to be.
You were probably assuming that was going to knock out some VBA, but as soon as I read this.
I said, I have the perfect array formula that solves this problem.
So, we are renting a Rail Car, from 4/18 to 8/13.
It's rail tank number 123 and the problem is the rates change.
So, from April 15th to May 10th, the rate is $35.
But May 11th, it changes to $40 and on July 11th it changes the $50.
What's the formula that will solve this?
Check this out.
I was so happy, that this worked, and it worked perfectly.
Let's build from the inside out.
So, here's my opening date and closing date.
I take the indirect of the opening date, colon, closing date and pass that to the ROW function, which returns a whole series of rows somewhere down in the 40,000.
But I'll have one row for every single date and this pops out to a huge array of all of the dates in that period and then I pass that to the old lookup function.
Why don't I use vlookup?
Because lookup is allowed to work with arrays of values.
The first argument of the lookup can be a whole series of values instead of just one value.
That does not work with vlookup, so I say hey! Go look up that date and here this is table 123, this is table 456.
Now, how did I create those?
I created those by selecting the range and coming up here in the name box and typing table 123.
Actually, it's table 123, I should have used that great Bob trick from, a few weeks ago.
Where if you go to 39 percent zoom, you actually get to see the name of each table.
Although, it is too small to actually see there, so not that good of a trick in this case.
But anyway the lookup table is table and the rail car number 123.
So, that's the table it finds the date because we're doing an approximate match, version of lookup.
It will find the date just lower than and return the rate for that day.
Finally, pass the entire thing to the SUM function and BAM!
We have our answer.
Put in a different rail car, different set of dates, and it continues to work.
Actually, I guess I have the wrong dates here.
So, this will be kind of a good check to go through and make sure that it recalculates.
There we go, all right! Mike, I'm sure you were expecting VBA.
I'm so proud of my array formula.
Your book must be working, everyone should go by Mike's Control Shift Enter, book because even I have learned how to do array formulas.
Mike, I'll see what you have.
Mike: That is amazing MrExcel.
I could not figure out a solution to this.
That was compact and efficient and that is amazing, my heavens.
This definitely earned you 10 points.
Oh man! Well, I'm gonna kind of repeat, what he does and change part of the lookup for the table and then calculate an average, instead of a total.
Now, of course this little bit right here, we have ROW and indirect and we concatenate the start and a colon and the end.
Now, let's just check this out, if we highlight this, what is so genius about this [ F9 ], is it gives us a bunch of serial numbers and guess what, now we can treat this as the first column of the lookup table and it's sorted 1 2 3.
Look up each one of those and it will just continually repeat, repeat, repeat, repeat until it gets the next one repeat, repeat, repeat.
Which is what we need if we're going to do an average and by the way, MrExcel said in the Control Shift Enter, book which I wrote page 87, it talks about that page 75 and 77, it talks about that lookup function argument.
You know, the thing is when I went to go and solve this I got stuck down a different thinking path than this.
It didn't matter that I had these formula tools at my disposal.
When I went to solve it, I was thinking in a different direction and never thought of these cool things.
You've got to be kidding me, absolutely right, if I give [ F9 ], that lookup value all these serial numbers.
That instructs the lookup to do a function argument array operation, which means return a bunch of items.
Now, the only trick is MrExcel did that cool thing with the table name.
I'm going to go ahead and create a dynamic range, using offset here, That way if we had a gigantic table as we copy the formula down, this Rail Tank number will be the trigger for a different range.
I'll use offset, the reference I'm always going to start here [ F4 ] comma how many rows from that position, do I want to go down.
I'm gonna look this up exact match with match, here it'll give me one down, here will give me one two three four.
So match, I'm looking up that within the entire column [ F4 ] comma zero exact match is what tells match to get the first one.
That's gonna give me how many rows go down columns.
Columns, I actually want to jump none over because I'm gonna start right there or right there.
So, I skip over that comma the height, COUNTIF.
Now, maybe there's always 3, right.
So you could just type 3 in to that height argument, but just in case, they have variable number of rail tank numbers.
[ F4 ], comma and will count.
Now, that'll give us the height, right there.
The width well, it's always gonna be 123 because that's our lookup table.
So, 3, close parenthesis.
Now, right now if I were to highlight this in...
Yeah! That's the entire offset and hit [ F9 ], it's delivering just those numbers right there.
First column is our lookup, third column is the one we're returning and of course, lookup when you give it the array here always gets from the last column.
You've got to be kidding me, that is genius.
Now, remember this is a function argument array operation because there's lots of them.
Someone look up, does this operation, boom!
It spits out all of the numbers perfectly repeated, to then do an average calculation.
Alright! So, now I just go AVERAGE.
Now, this is this AVERAGE function here is not problem, that number arguments not programmed to handle this array.
Spit out, so we have to use [ control shift and enter ] to get a calculator, if I hit [ enter ] it gives me 35.
It's only showing the first one through implicit intersection because I'm next to that data set, [ control shift enter ] There is the average, you gotta be kidding me.
So, the moral of the story for me is as so often, I get stuck down some trail of thought, totally ignoring some other beautiful potential solutions, MrExcel ten points, I'm going to thrown back to you.
MrExcel: Ten points, no! No!
Zero points, I got the formula right, but I didn't answer the right question.
He was looking for an average rate, and I gave a total rate change that sum to average, point to Mike.
Well, I thank everyone for stopping by, we'll see next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel, be joined by Mike Girvin from Excel Is Fun.
This is our episode 158.
Working on the Railroad.
Hey, Mike, all right! Welcome back time for another dueling Excel podcast.
Great appearance on Excel TV, those guys are a lot of fun, aren't they.
I hope to see you next Friday May 9th, across the country from you about as far as you can get from Washington down, Tampa, Florida.
I'll be doing our Excel live hope to see you down there.
I'm so excited you sent this question in and said you're not sure, what the answer is going to be.
You were probably assuming that was going to knock out some VBA, but as soon as I read this.
I said, I have the perfect array formula that solves this problem.
So, we are renting a Rail Car, from 4/18 to 8/13.
It's rail tank number 123 and the problem is the rates change.
So, from April 15th to May 10th, the rate is $35.
But May 11th, it changes to $40 and on July 11th it changes the $50.
What's the formula that will solve this?
Check this out.
I was so happy, that this worked, and it worked perfectly.
Let's build from the inside out.
So, here's my opening date and closing date.
I take the indirect of the opening date, colon, closing date and pass that to the ROW function, which returns a whole series of rows somewhere down in the 40,000.
But I'll have one row for every single date and this pops out to a huge array of all of the dates in that period and then I pass that to the old lookup function.
Why don't I use vlookup?
Because lookup is allowed to work with arrays of values.
The first argument of the lookup can be a whole series of values instead of just one value.
That does not work with vlookup, so I say hey! Go look up that date and here this is table 123, this is table 456.
Now, how did I create those?
I created those by selecting the range and coming up here in the name box and typing table 123.
Actually, it's table 123, I should have used that great Bob trick from, a few weeks ago.
Where if you go to 39 percent zoom, you actually get to see the name of each table.
Although, it is too small to actually see there, so not that good of a trick in this case.
But anyway the lookup table is table and the rail car number 123.
So, that's the table it finds the date because we're doing an approximate match, version of lookup.
It will find the date just lower than and return the rate for that day.
Finally, pass the entire thing to the SUM function and BAM!
We have our answer.
Put in a different rail car, different set of dates, and it continues to work.
Actually, I guess I have the wrong dates here.
So, this will be kind of a good check to go through and make sure that it recalculates.
There we go, all right! Mike, I'm sure you were expecting VBA.
I'm so proud of my array formula.
Your book must be working, everyone should go by Mike's Control Shift Enter, book because even I have learned how to do array formulas.
Mike, I'll see what you have.
Mike: That is amazing MrExcel.
I could not figure out a solution to this.
That was compact and efficient and that is amazing, my heavens.
This definitely earned you 10 points.
Oh man! Well, I'm gonna kind of repeat, what he does and change part of the lookup for the table and then calculate an average, instead of a total.
Now, of course this little bit right here, we have ROW and indirect and we concatenate the start and a colon and the end.
Now, let's just check this out, if we highlight this, what is so genius about this [ F9 ], is it gives us a bunch of serial numbers and guess what, now we can treat this as the first column of the lookup table and it's sorted 1 2 3.
Look up each one of those and it will just continually repeat, repeat, repeat, repeat until it gets the next one repeat, repeat, repeat.
Which is what we need if we're going to do an average and by the way, MrExcel said in the Control Shift Enter, book which I wrote page 87, it talks about that page 75 and 77, it talks about that lookup function argument.
You know, the thing is when I went to go and solve this I got stuck down a different thinking path than this.
It didn't matter that I had these formula tools at my disposal.
When I went to solve it, I was thinking in a different direction and never thought of these cool things.
You've got to be kidding me, absolutely right, if I give [ F9 ], that lookup value all these serial numbers.
That instructs the lookup to do a function argument array operation, which means return a bunch of items.
Now, the only trick is MrExcel did that cool thing with the table name.
I'm going to go ahead and create a dynamic range, using offset here, That way if we had a gigantic table as we copy the formula down, this Rail Tank number will be the trigger for a different range.
I'll use offset, the reference I'm always going to start here [ F4 ] comma how many rows from that position, do I want to go down.
I'm gonna look this up exact match with match, here it'll give me one down, here will give me one two three four.
So match, I'm looking up that within the entire column [ F4 ] comma zero exact match is what tells match to get the first one.
That's gonna give me how many rows go down columns.
Columns, I actually want to jump none over because I'm gonna start right there or right there.
So, I skip over that comma the height, COUNTIF.
Now, maybe there's always 3, right.
So you could just type 3 in to that height argument, but just in case, they have variable number of rail tank numbers.
[ F4 ], comma and will count.
Now, that'll give us the height, right there.
The width well, it's always gonna be 123 because that's our lookup table.
So, 3, close parenthesis.
Now, right now if I were to highlight this in...
Yeah! That's the entire offset and hit [ F9 ], it's delivering just those numbers right there.
First column is our lookup, third column is the one we're returning and of course, lookup when you give it the array here always gets from the last column.
You've got to be kidding me, that is genius.
Now, remember this is a function argument array operation because there's lots of them.
Someone look up, does this operation, boom!
It spits out all of the numbers perfectly repeated, to then do an average calculation.
Alright! So, now I just go AVERAGE.
Now, this is this AVERAGE function here is not problem, that number arguments not programmed to handle this array.
Spit out, so we have to use [ control shift and enter ] to get a calculator, if I hit [ enter ] it gives me 35.
It's only showing the first one through implicit intersection because I'm next to that data set, [ control shift enter ] There is the average, you gotta be kidding me.
So, the moral of the story for me is as so often, I get stuck down some trail of thought, totally ignoring some other beautiful potential solutions, MrExcel ten points, I'm going to thrown back to you.
MrExcel: Ten points, no! No!
Zero points, I got the formula right, but I didn't answer the right question.
He was looking for an average rate, and I gave a total rate change that sum to average, point to Mike.
Well, I thank everyone for stopping by, we'll see next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
