The rent for a storage container starts out at $5 a day for the first 7 days, then $10 a day for the next two weeks, then $20 a day after that. How do you calculate the total rent? In this episode, Bill shows a pair of VBA User Defined Functions and Mike shows a pair of formulas.
Transcript of the video:
Bill: Hey. Welcome back. It's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel. I’ll be joined by Mike Girvin from ExcelIsFun.
This is our episode 136, calculate rent on a sliding scale.
Alright. Today's question, they have a container that they are renting and the rent is calculated per day but it changes as a sliding scale.
So, the first week, $5 a day, second 2 weeks, $10 per day, and then after that, it ramps up to $20 a day.
They really must want to get this container back.
So, for the first, for example, 41 days, $5 a day * the first 7 days, $10 a day * the next 14 days, and then we have 7 + 14, 21, 41 - 21 is 20 days * $20.
575.
That sounds like one of those sliding income tax scale problems that I'm sure you've run into along the way, but rather than do all that hard brain work to figure it out, I'm just going to switch over to VBA.
ALT+F11.
Insert module, a new function called RENT, and it's the number of days.
This is the most inefficient code in the world but it required no thinking at all on my part.
For i = 1 to the number of days, so then 41, we're actually going to loop through here 41 times.
Yeah.
This is inefficient but that's ok.
SELECT CASE i.
Which day is it?
If it’s day 1 to 7, we had $5, days 8 to 21, add $10.
After that, we're adding $20.
Boy, I'd hate to throw a 1000 to this, but it looks like they're only running it for 40, 50, 60 days.
We’ll switch back here.
ALT+F11.
=RENT, and we're going to watch just how long it takes to calculate.
It's probably going to be 30, 40 seconds.
No, it's really fast.
Who cares how inefficient it was, unless I have a million of these or a thousand of these.
It's calculating just as fast as any other formula would calculate.
So, there.
There we go.
A horribly inefficient user-defined function, get the problem done, and go on our way.
Mike, let's see what you have.
[ =RENT(A2) ] Mike: Thanks, MrExcel.
Hey, MrExcel wins for 2 reasons.
Rent with VBA in that case, absolutely awesome.
Now, the second reason that he's going to get five points this time instead of one is because he…I couldn't figure this out and I sent him an email that said I can’t figure this out, and he goes, oh, it's just like a tax problem, and I was like, ah, couldn't believe that I was stuck down some channel, couldn't figure it out, MrExcel comes to the rescue.
Now, he said case was cheap, here's a cheap way to do it.
We can just use the IF.
Now, for big tables, if we have lots of categories, this IF would not work, but, hey, I'm just going to say IF this is < 8, then please just give me whatever's in this cell * the 5, otherwise…and we still have a couple situations left, so in the VALUE IF FALSE, we say, IF.
Now, in this case, we're between 7 and 21.
So, we're going to use the AND function.
I'm going to say if this is > 7 and this is < 22, 1 above the upper limit for this category, IF both of those conditions are true in the AND, then what do we want?
Well, we have the base from the previous category of 35 + this – the 7 we've already accounted for, * the amount for this category, 10.
Otherwise, and there's only one case left, well, we had 175, that's if you added from the previous 2 categories, + whatever in () is here – the 21 already accounted for * the 20.
Wow.
I'm going to ) ). Look for that black, CONTROL+ENTER, double-click, and send it down.
[ =IF(A2<8,A2*5,IF(AND(A2>7,A2<22),35+(A2-7)*10,175+(A2-21)*20)) ] Now, that'll work just fine, but imagine if you had many categories, you'd create this huge formula.
Now, the alternative is to do a LOOKUP, and just like MrExcel said for case, it's super easy just to write it out.
This is probably super easy because sometimes the tables that you'd have to use for taxes or piecemeal or rent, like this, can be tricky to create.
They're worth creating if you have lots of categories.
Now, I have a video Excel Magic Trick 453 that does VLOOKUP for a tax commission table and Excel Magic Trick 825 for a piecemeal that's similar to this example.
Oh man.
So, I built this table here, and when you're doing a LOOKUP like this, we have to look up multiple things.
So, I always find it easy to take one example…a hundred so for this 41 right here, 175 +, we're going to have to take 41-21.
That's already accounted for * 20, I write that out, and then I look how that relates to doing a VLOOKUP.
This 175, as this table I set up, would be in the fifth column.
Then I say + the cell, the 41, and then a VLOOKUP.
Well, the amount to subtract from the 41 would be in the fourth column, that 21 right there, finally close off the ), and VLOOKUP the third column because that has the amount.
So, you do something like LOOKUP.
I'm going to say, hey, look up that , within our big huge table, F4, and this is column 5, right?
So, that would return all the way down just the amount from the previous brackets.
Now, I'm going to copy this VLOOKUP, CONTROL+C, and just change the column each time, + and then we have to take the units from here – and this VLOOKUP.
Well, we're subtracting NUMBER MADE THROUGH THE PREVIOUS CATEGORY, so we need that 21.
So that's column 4.
We have to close this ) off * another VLOOKUP.
That is looking up the amount right here.
That's in the third column so I simply change this to a 3.
CONTROL+ENTER, double-click, and send it down.
[ =VLOOKUP(A2,$B$12:$F$14,5)+(A2-VLOOKUP(A2,$B$12:$F$14,4))*VLOOKUP(A2,$B$12:$F$14,3) ] Alright.
Thanks to MrExcel for saving me.
I was stuck down some weird conceptual rut, unable to see this problem as a simple tax…various rate tax table piecemeal lookup.
Alright.
Send it back to MrExcel.
Bill: Hey, Mike.
That's a great formula.
Made me think that, you know…I complained this morning when I initially recorded this that the logic would be too difficult.
So, I just did this horrible loop here.
I took your logic from the formula and just said, hey, let's do SELECT CASE DAYS.
If it's > 21, we have 7 * 5, 14 * 10, and then days – 21, *20.
If the rent…or the days is > 7, then 7 * 5 + days – 7 * 10.
Otherwise, it’s just days * 5.
So, it's those 3 conditions you have in the IF statement, but here in the VBA, and we'll just come back, and so there's the = RENT, getting the exact same answers that we did.
=R-N-T getting the exact same answers as R-E-N-T.
So, lots of different ways to solve this problem.
So, I helped you out with the, hey, it's just like a tax problem, and you help me out with that logic is not as bad as you think it is.
So, there you go.
Well, hey.
I want to thank everybody for stopping by.
We'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.
I'm Bill Jelen from MrExcel. I’ll be joined by Mike Girvin from ExcelIsFun.
This is our episode 136, calculate rent on a sliding scale.
Alright. Today's question, they have a container that they are renting and the rent is calculated per day but it changes as a sliding scale.
So, the first week, $5 a day, second 2 weeks, $10 per day, and then after that, it ramps up to $20 a day.
They really must want to get this container back.
So, for the first, for example, 41 days, $5 a day * the first 7 days, $10 a day * the next 14 days, and then we have 7 + 14, 21, 41 - 21 is 20 days * $20.
575.
That sounds like one of those sliding income tax scale problems that I'm sure you've run into along the way, but rather than do all that hard brain work to figure it out, I'm just going to switch over to VBA.
ALT+F11.
Insert module, a new function called RENT, and it's the number of days.
This is the most inefficient code in the world but it required no thinking at all on my part.
For i = 1 to the number of days, so then 41, we're actually going to loop through here 41 times.
Yeah.
This is inefficient but that's ok.
SELECT CASE i.
Which day is it?
If it’s day 1 to 7, we had $5, days 8 to 21, add $10.
After that, we're adding $20.
Boy, I'd hate to throw a 1000 to this, but it looks like they're only running it for 40, 50, 60 days.
We’ll switch back here.
ALT+F11.
=RENT, and we're going to watch just how long it takes to calculate.
It's probably going to be 30, 40 seconds.
No, it's really fast.
Who cares how inefficient it was, unless I have a million of these or a thousand of these.
It's calculating just as fast as any other formula would calculate.
So, there.
There we go.
A horribly inefficient user-defined function, get the problem done, and go on our way.
Mike, let's see what you have.
[ =RENT(A2) ] Mike: Thanks, MrExcel.
Hey, MrExcel wins for 2 reasons.
Rent with VBA in that case, absolutely awesome.
Now, the second reason that he's going to get five points this time instead of one is because he…I couldn't figure this out and I sent him an email that said I can’t figure this out, and he goes, oh, it's just like a tax problem, and I was like, ah, couldn't believe that I was stuck down some channel, couldn't figure it out, MrExcel comes to the rescue.
Now, he said case was cheap, here's a cheap way to do it.
We can just use the IF.
Now, for big tables, if we have lots of categories, this IF would not work, but, hey, I'm just going to say IF this is < 8, then please just give me whatever's in this cell * the 5, otherwise…and we still have a couple situations left, so in the VALUE IF FALSE, we say, IF.
Now, in this case, we're between 7 and 21.
So, we're going to use the AND function.
I'm going to say if this is > 7 and this is < 22, 1 above the upper limit for this category, IF both of those conditions are true in the AND, then what do we want?
Well, we have the base from the previous category of 35 + this – the 7 we've already accounted for, * the amount for this category, 10.
Otherwise, and there's only one case left, well, we had 175, that's if you added from the previous 2 categories, + whatever in () is here – the 21 already accounted for * the 20.
Wow.
I'm going to ) ). Look for that black, CONTROL+ENTER, double-click, and send it down.
[ =IF(A2<8,A2*5,IF(AND(A2>7,A2<22),35+(A2-7)*10,175+(A2-21)*20)) ] Now, that'll work just fine, but imagine if you had many categories, you'd create this huge formula.
Now, the alternative is to do a LOOKUP, and just like MrExcel said for case, it's super easy just to write it out.
This is probably super easy because sometimes the tables that you'd have to use for taxes or piecemeal or rent, like this, can be tricky to create.
They're worth creating if you have lots of categories.
Now, I have a video Excel Magic Trick 453 that does VLOOKUP for a tax commission table and Excel Magic Trick 825 for a piecemeal that's similar to this example.
Oh man.
So, I built this table here, and when you're doing a LOOKUP like this, we have to look up multiple things.
So, I always find it easy to take one example…a hundred so for this 41 right here, 175 +, we're going to have to take 41-21.
That's already accounted for * 20, I write that out, and then I look how that relates to doing a VLOOKUP.
This 175, as this table I set up, would be in the fifth column.
Then I say + the cell, the 41, and then a VLOOKUP.
Well, the amount to subtract from the 41 would be in the fourth column, that 21 right there, finally close off the ), and VLOOKUP the third column because that has the amount.
So, you do something like LOOKUP.
I'm going to say, hey, look up that , within our big huge table, F4, and this is column 5, right?
So, that would return all the way down just the amount from the previous brackets.
Now, I'm going to copy this VLOOKUP, CONTROL+C, and just change the column each time, + and then we have to take the units from here – and this VLOOKUP.
Well, we're subtracting NUMBER MADE THROUGH THE PREVIOUS CATEGORY, so we need that 21.
So that's column 4.
We have to close this ) off * another VLOOKUP.
That is looking up the amount right here.
That's in the third column so I simply change this to a 3.
CONTROL+ENTER, double-click, and send it down.
[ =VLOOKUP(A2,$B$12:$F$14,5)+(A2-VLOOKUP(A2,$B$12:$F$14,4))*VLOOKUP(A2,$B$12:$F$14,3) ] Alright.
Thanks to MrExcel for saving me.
I was stuck down some weird conceptual rut, unable to see this problem as a simple tax…various rate tax table piecemeal lookup.
Alright.
Send it back to MrExcel.
Bill: Hey, Mike.
That's a great formula.
Made me think that, you know…I complained this morning when I initially recorded this that the logic would be too difficult.
So, I just did this horrible loop here.
I took your logic from the formula and just said, hey, let's do SELECT CASE DAYS.
If it's > 21, we have 7 * 5, 14 * 10, and then days – 21, *20.
If the rent…or the days is > 7, then 7 * 5 + days – 7 * 10.
Otherwise, it’s just days * 5.
So, it's those 3 conditions you have in the IF statement, but here in the VBA, and we'll just come back, and so there's the = RENT, getting the exact same answers that we did.
=R-N-T getting the exact same answers as R-E-N-T.
So, lots of different ways to solve this problem.
So, I helped you out with the, hey, it's just like a tax problem, and you help me out with that logic is not as bad as you think it is.
So, there you go.
Well, hey.
I want to thank everybody for stopping by.
We'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.
