Two very odd discount tier questions came in today. In this question, all of the units up to the discount tier do not get the discount. Only the units above the tier get the discount. And, there are 4 levels. Rather than the long nested IF statement, I try a combination of LOOKUP, INDIRECT, ROW in an Excel array formula.
Transcript of the video:
The MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1955 - Discount Tiers, Where only the units above the Tier get the discount!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question is sent all the way from Pakistan.
Mohammed has a problem where he has to figure out the total amount of charges.
And, you know, here's the question, nothing and ears me more than demanding a video in all caps with very little explanation.
And, check this out a huge massive merged cell!
But here we are, we're doing this video anyway.
So the question, and he has given an example so you can see that the tiers here, here's the number of units.
So if it's less than 100 you're just paying the units times the charge, simple enough.
Ah but then, if it's less than 200, and of course, you know, that would only be the things from 101 to 200, you're paying the B4-100, times 1.1.
So there's an upcharge, this must be like some sort of electricity billing, where you pay more if you use too much.
And then whatever is at the previous tier, 100-0 times the original amount.
And you can see there's another tier here for 300, and the formula just keeps getting longer and longer and longer.
And here in the last one, so you're paying everything above 300 times 125%, everything from 300 to 200, which, hey let's face it, that's really just a 100, times 1.2, 120%, and then everything from 200 to 101.1%.
And boy, this formula is really confusing, there has to be an easier way to do this, I'm going to abandon the IF statements all together, let's just come up with a nice quick solution, alright?
And the first thing I want to do, is I want to do one of those weird VLOOKUP things, where we are having just ranges.
I'm actually going to put TRUE at the end of the VLOOKUP, so =VLOOKUP of this unit number, how much we're paying for this particular unit number in this table down here, and we'll press F4 to lock that down.
We want second column, and we are doing the very rare approximate match so, you could leave the TRUE off, but I'm going to put the TRUE there just to remind myself what's happening.
So here's how it works, unit number 1, you're paying 100%, unit number 99, you're paying 100%.
But when you get to unit 110, then you're paying 110%, when you get the unit number 220, you're paying 120%.
Alright, so this little VLOOKUP here is saying for this particular unit how much are we going to pay.
It really comes down to the simple little situation here, where we're going from X=1 to whatever is in B4, in this case from 1-400, do a VLOOKUP of that X into the table, get the second column, ,TRUE , and then, really I guess I left it off here, we're going to multiply that times C4, and the sum of all of those will come up with our answer.
It won't require a whole bunch of nested IF statements.
Now, when you set up the table, the table has to go from lowest to highest.
And in the original question, right I mean, we're looking at things <100, <200, <300, the table has to be a little bit different.
You have to handle everything from all the way down at the bottom, from zero, from unit number 0, they're going to be paying 100%.
When you get the unit 101, that's where they start paying 110%.
Alright so, let's take a look at the steps here.
The first thing I want to do is do, say that we want to go from ="1:"&B4 , which is going to build a nice little reference.
So, it's saying "We're going from row 1-400", and there's the formula over there.
And then what we're going to do is, we're going to take that thing that looks like a cell reference, I'll do a CTRL+" here to bring that formula down, and let's click in and say "I want to do =INDIRECT", that thing that looks like a cell reference.
Now this is actually going to point to all rows from 1-400.
Now this actually creates a circular reference, but I'm not using this formula its current state in the final solution, so that's okay.
Alright, now that we have an indirect point into rows 1-400, I need that to generate every number from 1-400, so CTRL+" again brings that exact same formula down.
I ask for the =ROW, and then the whole big thing.
Which is, if I just press Enter, it's going to return the number 1.
Alright, but when I later press CTRL+Shift+Enter, it's going to return all of the numbers from 1-400.
OK, now here's the thing that's beautiful.
I want to do a VLOOKUP of every number from 1-400, and I now have a formula that's generating all the numbers from 1-400, but VLOOKUP cannot do this, you can't, in this X here, put the numbers 1-400.
There is however an ancient, ancient, ancient version of VLOOKUP that can do that.
Alright, and instead of VLOOKUP, it's just simply =LOOKUP, this beautiful thing can handle an array as the first argument.
And then, out here is our table.
The funny thing about LOOKUP is, you don't have to say which column you're looking for, and you don't have to say TRUE.
It always looks for the last column in the table, and it always defaults to TRUE, which is usually maddening, but in this particular case for Mohammed in Pakistan, it is a beautiful, beautiful thing.
Alright, so right now, when I press Enter, all it's going to do, is going to do the very first LOOKUP, which is going to give me 100%, because I'm looking up the number 1 in this table, alright?
And we get the number 1 as expected.
But if instead we would hold down CTRL+Shift+Enter, it then goes through and does all 400 LOOKUPs.
Now, because I want to get 400 answers, I have to send that into some sort of a wrapper function.
So we're going to use SUM today to sum that whole thing.
And here, watch this, CTRL+Shift+Enter is going to go through, it's adding up 400 numbers, but not 400 at 100%, there's some at 100%, some at 110%, some at 120%, some at 135%.
And so we are getting the right answer.
Now we still have to multiply that by C4, that's easy to do, *C4 out here, and again CTRL+Shift+Enter, and there is the 3185!
That was the answer given in the original example, right?
Isn't that a beautiful, short, short formula?
Alright so, hey, and let's do one more thing, let's take this formula one more time, and we don't want to have to leave this lookup table right there in the in the worksheet.
So we'll select the lookup table and press F9, that, I'll bet, embed an array constant, and there again, CTRL+Shift+Enter.
There's my formula, we'll take that formula, copy it from the formula bar, CTRL+C, come up here to D4, and I will paste that in.
Don't forget the CTRL+Shift+Enter, if you don't press CTRL+Shift+Enter, you're just going to get a wrong answer.
So you have to hold down CTRL and Shift, and then press Enter!
Alright, once I have that, I can copy that down to the other cells, and it is doing the correct calculation.
Alright now, the hassle here is, this is the example, they want to make sure that you can understand this in real life.
So you know, here's a nice little summary of the whole video so far: How the formula works, you can pause the video at this point, and read through the various steps to remember how it works.
But then, after this example question, there apparently is the actual question, the real life question.
And again, very poorly worded, looks like whoever set this up (Mohammed's manager) didn't bother to put the brakes at the right point.
So as best as I can tell, this translates to this table, where we have 0, 75, 180, 300.
At 0 you're paying 100%, and now there's actually a discount, above 75 you're paying 92.5%, and so on and so on and so on.
And so the question is, can we apply what we just learned with the other example to this?
OK, pasting the old formula in, I had to adjust a couple of things here to make sure they're importing to now(?).
So C16 instead of B4, and D16 instead of C4.
Also, pointing to the new LOOKUP table, you have to do that first, it's easier than typing the array constant.
And again, here's a trick that I borrowed from Mike Girvin- that I LEARNED from Mike Girvin, just select that and press F9 to embed that.
Again CTRL+Shift+Enter, there's our answer.
And then copy, notice you have to do a copy to a brand new range, and it works all the way down.
Now this would have been a brilliant question for the Dueling Excel podcast, which normally happens on a Friday.
Mohammed needed this for his manager by Thursday, so there was a rush, we had to get it done now now now, quick!
So there you go, one solution to avoiding this huge massive nested IF statement.
Hey, I want to thank Mohammed for screaming that question at me, and I want to thank you for stopping, by we'll see you next time for another netcast for MrExcel!
Learn Excel from MrExcel podcast, episode 1955 - Discount Tiers, Where only the units above the Tier get the discount!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question is sent all the way from Pakistan.
Mohammed has a problem where he has to figure out the total amount of charges.
And, you know, here's the question, nothing and ears me more than demanding a video in all caps with very little explanation.
And, check this out a huge massive merged cell!
But here we are, we're doing this video anyway.
So the question, and he has given an example so you can see that the tiers here, here's the number of units.
So if it's less than 100 you're just paying the units times the charge, simple enough.
Ah but then, if it's less than 200, and of course, you know, that would only be the things from 101 to 200, you're paying the B4-100, times 1.1.
So there's an upcharge, this must be like some sort of electricity billing, where you pay more if you use too much.
And then whatever is at the previous tier, 100-0 times the original amount.
And you can see there's another tier here for 300, and the formula just keeps getting longer and longer and longer.
And here in the last one, so you're paying everything above 300 times 125%, everything from 300 to 200, which, hey let's face it, that's really just a 100, times 1.2, 120%, and then everything from 200 to 101.1%.
And boy, this formula is really confusing, there has to be an easier way to do this, I'm going to abandon the IF statements all together, let's just come up with a nice quick solution, alright?
And the first thing I want to do, is I want to do one of those weird VLOOKUP things, where we are having just ranges.
I'm actually going to put TRUE at the end of the VLOOKUP, so =VLOOKUP of this unit number, how much we're paying for this particular unit number in this table down here, and we'll press F4 to lock that down.
We want second column, and we are doing the very rare approximate match so, you could leave the TRUE off, but I'm going to put the TRUE there just to remind myself what's happening.
So here's how it works, unit number 1, you're paying 100%, unit number 99, you're paying 100%.
But when you get to unit 110, then you're paying 110%, when you get the unit number 220, you're paying 120%.
Alright, so this little VLOOKUP here is saying for this particular unit how much are we going to pay.
It really comes down to the simple little situation here, where we're going from X=1 to whatever is in B4, in this case from 1-400, do a VLOOKUP of that X into the table, get the second column, ,TRUE , and then, really I guess I left it off here, we're going to multiply that times C4, and the sum of all of those will come up with our answer.
It won't require a whole bunch of nested IF statements.
Now, when you set up the table, the table has to go from lowest to highest.
And in the original question, right I mean, we're looking at things <100, <200, <300, the table has to be a little bit different.
You have to handle everything from all the way down at the bottom, from zero, from unit number 0, they're going to be paying 100%.
When you get the unit 101, that's where they start paying 110%.
Alright so, let's take a look at the steps here.
The first thing I want to do is do, say that we want to go from ="1:"&B4 , which is going to build a nice little reference.
So, it's saying "We're going from row 1-400", and there's the formula over there.
And then what we're going to do is, we're going to take that thing that looks like a cell reference, I'll do a CTRL+" here to bring that formula down, and let's click in and say "I want to do =INDIRECT", that thing that looks like a cell reference.
Now this is actually going to point to all rows from 1-400.
Now this actually creates a circular reference, but I'm not using this formula its current state in the final solution, so that's okay.
Alright, now that we have an indirect point into rows 1-400, I need that to generate every number from 1-400, so CTRL+" again brings that exact same formula down.
I ask for the =ROW, and then the whole big thing.
Which is, if I just press Enter, it's going to return the number 1.
Alright, but when I later press CTRL+Shift+Enter, it's going to return all of the numbers from 1-400.
OK, now here's the thing that's beautiful.
I want to do a VLOOKUP of every number from 1-400, and I now have a formula that's generating all the numbers from 1-400, but VLOOKUP cannot do this, you can't, in this X here, put the numbers 1-400.
There is however an ancient, ancient, ancient version of VLOOKUP that can do that.
Alright, and instead of VLOOKUP, it's just simply =LOOKUP, this beautiful thing can handle an array as the first argument.
And then, out here is our table.
The funny thing about LOOKUP is, you don't have to say which column you're looking for, and you don't have to say TRUE.
It always looks for the last column in the table, and it always defaults to TRUE, which is usually maddening, but in this particular case for Mohammed in Pakistan, it is a beautiful, beautiful thing.
Alright, so right now, when I press Enter, all it's going to do, is going to do the very first LOOKUP, which is going to give me 100%, because I'm looking up the number 1 in this table, alright?
And we get the number 1 as expected.
But if instead we would hold down CTRL+Shift+Enter, it then goes through and does all 400 LOOKUPs.
Now, because I want to get 400 answers, I have to send that into some sort of a wrapper function.
So we're going to use SUM today to sum that whole thing.
And here, watch this, CTRL+Shift+Enter is going to go through, it's adding up 400 numbers, but not 400 at 100%, there's some at 100%, some at 110%, some at 120%, some at 135%.
And so we are getting the right answer.
Now we still have to multiply that by C4, that's easy to do, *C4 out here, and again CTRL+Shift+Enter, and there is the 3185!
That was the answer given in the original example, right?
Isn't that a beautiful, short, short formula?
Alright so, hey, and let's do one more thing, let's take this formula one more time, and we don't want to have to leave this lookup table right there in the in the worksheet.
So we'll select the lookup table and press F9, that, I'll bet, embed an array constant, and there again, CTRL+Shift+Enter.
There's my formula, we'll take that formula, copy it from the formula bar, CTRL+C, come up here to D4, and I will paste that in.
Don't forget the CTRL+Shift+Enter, if you don't press CTRL+Shift+Enter, you're just going to get a wrong answer.
So you have to hold down CTRL and Shift, and then press Enter!
Alright, once I have that, I can copy that down to the other cells, and it is doing the correct calculation.
Alright now, the hassle here is, this is the example, they want to make sure that you can understand this in real life.
So you know, here's a nice little summary of the whole video so far: How the formula works, you can pause the video at this point, and read through the various steps to remember how it works.
But then, after this example question, there apparently is the actual question, the real life question.
And again, very poorly worded, looks like whoever set this up (Mohammed's manager) didn't bother to put the brakes at the right point.
So as best as I can tell, this translates to this table, where we have 0, 75, 180, 300.
At 0 you're paying 100%, and now there's actually a discount, above 75 you're paying 92.5%, and so on and so on and so on.
And so the question is, can we apply what we just learned with the other example to this?
OK, pasting the old formula in, I had to adjust a couple of things here to make sure they're importing to now(?).
So C16 instead of B4, and D16 instead of C4.
Also, pointing to the new LOOKUP table, you have to do that first, it's easier than typing the array constant.
And again, here's a trick that I borrowed from Mike Girvin- that I LEARNED from Mike Girvin, just select that and press F9 to embed that.
Again CTRL+Shift+Enter, there's our answer.
And then copy, notice you have to do a copy to a brand new range, and it works all the way down.
Now this would have been a brilliant question for the Dueling Excel podcast, which normally happens on a Friday.
Mohammed needed this for his manager by Thursday, so there was a rush, we had to get it done now now now, quick!
So there you go, one solution to avoiding this huge massive nested IF statement.
Hey, I want to thank Mohammed for screaming that question at me, and I want to thank you for stopping, by we'll see you next time for another netcast for MrExcel!
