You have a different rate per category, and half of the categories have a Max but the others do not. Mike and Bill offer different options for calculating the rate in Excel.
Transcript of the video:
Hey, welcome back, it's time for another dueling Excel podcast.
I'm Bill Jelen from MrExcel will be joined by Mike Girvin ExcelIsFun.
This is our episode 143: VLOOKUP the Rates, but not to exceed MAX in some cases.
Bill Jelen: All right, Mike.
Hey!
Here's a doozy sent in from YouTube.
I think they watched our vlookup week videos, but still trying to solve this problem.
So, we have an item category here right with data validation.
Over here, this was actually on another sheet but I brought it back it's a list of all the categories and the fees so the fee is this £99 times the vlookup to get the right percentage.
But, for some of the categories there's a max amount and so for those four categories, then we want to make sure that this calculation doesn't exceed that and I'm thinking trying to figure out.
I'm going to use is blank, I'm going to look vlookup max, I'm going to send it over to you and let's see what you have first.
Mike Girvin: Thanks MrExcel.
Hey, I'm not so sure.
I have an elegant, compact way to do this.
I'm just going to have to slap a bunch of vlookups into one cell.
You know and as you look at this table, it's this third column here that's the trigger right?
It's either got nothing in it or a max amount.
So, I'm going to start with =VLOOKUP (A2, $E$2: $G$10, 3,0) and look up that third column.
So, I'm going to highlight the F4 comma third column and then comma 0 for exact match.
Now what will happen is those empty cells will return a zero.
Double click and send that down.
Now I'm going to use that in the “VLOOKUP Logical Test” because any nonzero number like 40, 10 will be true and easy row will be false.
So, I'm going to hit F2 and I'll use that as the trigger IF, right?
Because we have two situations we either need to compare the max to whatever the calculation is or we just run the straight this.
Now running the just the percentages where there's no max that's going to be the value of false.
So, the value if true?
Hey, I need the MIN of either CTRL+V. Either I'm in to copy this copy either CTRL+V from the second so we're going to get that percentage from this column here and multiply it by this or the second MIN value will be CTRL+V we're looking up the item the max value from the third column.
So, when I close off the min I'm back at these screen tips are awesome value if true.
Now I type a comma and the value if false I have to run the vlookup again.
So, this will be from the second column times one cell to my left.
Now technically what will happen is it, it will run the vlookup in the logical test.
If it’s false it'll have to run vlookup twice.
If it's true, it'll have to run vlookup one, two, three times plus a min.
All right CTRL+ENTER and copy it down and so it looks like here instead of getting whatever the percentage of furniture is 10% times 855 and went ahead and got the 40 bucks the rest of them look okay too.
All right, send it back to MrExcel.
Bill Jelen: Hey, all right Mike I love that using the vlookup of the third column as the logical test if it's blank it turns into a zero which becomes a false.
Otherwise, we have the value and we have to go look at the MIN.
Now I have a different approach to this.
I'm going to start off a little bit simpler where we just simply calculate the fee.
The fee is the =VLOOKUP (A2, $E$2: $F$10, 2, False) *B2 of this category into this table F4 comma 2 comma false times the sales price.
All right, so we'll copy that down and for every category we're taking the sales price times the fee associated with that category and you know that's step one.
Now, step two is sometimes we have to apply the MAX amount and I don't like sometimes.
I want formulas to be able to work all the time not just sometimes.
So, this is eBay right?
These are ebay categories and ebay fees and ebay max amounts.
Well, that you know these items that don't have a max amount they really, truly do have a max amount.
I mean, come on, face it you've shopped eBay before, what's the largest purchase you've ever made on eBay?
There certainly is a max amount like there would never be a fee that's more than 99 99 999.
That would mean that you sold something for not just 99 million pounds but 99 million pounds divided by 11% almost a billion dollars.
I've never seen anything on eBay being sold for a billion dollars.
So, let's just put that max amount in there and then for every category we can assume that there is a max and we treat all the categories the same.
I also want to take a step back and explain why we use the MIN to figure out if we've hit the max or not.
So, here's just a fictitious calculation.
We have numbers here and we have e basing.
Hey, the Max's calculation can be as 750 so what's the answer the logical way that most people would think about this is +IF (B2<A2, B2, A2) if the max is less than the calculation then we use the max otherwise we use the calculation.
I think that makes sense to a lot of people that that's just how we think about it, we look at this and choose one or the other.
Turns out that what these answers here are the exact same answers we would get if we use the MIN of the two items.
So, that one's the same, same, same, same, same, same and what we're doing here is we're setting up kind of a race.
All right, and we want to see who has the lowest score and in this case 685 vs 750 which one is lowest?
If the calculation is lowest that's what wins.
But here where the calculation is 890 versus 750 the 750 which is in the column called max is the fastest score lowest score and so it went in the min.
So, the min is a shorter way to do this calculation and we don't have all of the extra in essence, there's four references here and only two references there, some might argue that that's one reference but it's two sales and that one reference.
So, coming back here we have our calculation and for every single category we're going to look for this MIN of the original calculation comma and then the max for that category so =MIN(VLOOKUP(A2,$E$2:$F$10,2,False)*B2,VLOOKUP(A2,$E$2:$G$10,3,False)) vlookup of I'll have to press f2 there to go back into point mode of that category go over three columns f 4 comma 3 comma false parentheses close the vlookup parentheses to close the min and double-click to shoot that down and you see that some of these items like the 855 times 10% change to $40 because of the MIN.
The other items which never got close to the MAX they stayed exactly the way that they were.
So, by coming back here to this table adding in the really big numbers.
Numbers that are larger than any number that could possibly happen from this calculation we get to use one set of logic instead of having to test for both sets false.
Hey, I want to thank everyone for stopping by.
See you next time for another dueling Excel podcast from MrExcel and ExcelIsFun.
I'm Bill Jelen from MrExcel will be joined by Mike Girvin ExcelIsFun.
This is our episode 143: VLOOKUP the Rates, but not to exceed MAX in some cases.
Bill Jelen: All right, Mike.
Hey!
Here's a doozy sent in from YouTube.
I think they watched our vlookup week videos, but still trying to solve this problem.
So, we have an item category here right with data validation.
Over here, this was actually on another sheet but I brought it back it's a list of all the categories and the fees so the fee is this £99 times the vlookup to get the right percentage.
But, for some of the categories there's a max amount and so for those four categories, then we want to make sure that this calculation doesn't exceed that and I'm thinking trying to figure out.
I'm going to use is blank, I'm going to look vlookup max, I'm going to send it over to you and let's see what you have first.
Mike Girvin: Thanks MrExcel.
Hey, I'm not so sure.
I have an elegant, compact way to do this.
I'm just going to have to slap a bunch of vlookups into one cell.
You know and as you look at this table, it's this third column here that's the trigger right?
It's either got nothing in it or a max amount.
So, I'm going to start with =VLOOKUP (A2, $E$2: $G$10, 3,0) and look up that third column.
So, I'm going to highlight the F4 comma third column and then comma 0 for exact match.
Now what will happen is those empty cells will return a zero.
Double click and send that down.
Now I'm going to use that in the “VLOOKUP Logical Test” because any nonzero number like 40, 10 will be true and easy row will be false.
So, I'm going to hit F2 and I'll use that as the trigger IF, right?
Because we have two situations we either need to compare the max to whatever the calculation is or we just run the straight this.
Now running the just the percentages where there's no max that's going to be the value of false.
So, the value if true?
Hey, I need the MIN of either CTRL+V. Either I'm in to copy this copy either CTRL+V from the second so we're going to get that percentage from this column here and multiply it by this or the second MIN value will be CTRL+V we're looking up the item the max value from the third column.
So, when I close off the min I'm back at these screen tips are awesome value if true.
Now I type a comma and the value if false I have to run the vlookup again.
So, this will be from the second column times one cell to my left.
Now technically what will happen is it, it will run the vlookup in the logical test.
If it’s false it'll have to run vlookup twice.
If it's true, it'll have to run vlookup one, two, three times plus a min.
All right CTRL+ENTER and copy it down and so it looks like here instead of getting whatever the percentage of furniture is 10% times 855 and went ahead and got the 40 bucks the rest of them look okay too.
All right, send it back to MrExcel.
Bill Jelen: Hey, all right Mike I love that using the vlookup of the third column as the logical test if it's blank it turns into a zero which becomes a false.
Otherwise, we have the value and we have to go look at the MIN.
Now I have a different approach to this.
I'm going to start off a little bit simpler where we just simply calculate the fee.
The fee is the =VLOOKUP (A2, $E$2: $F$10, 2, False) *B2 of this category into this table F4 comma 2 comma false times the sales price.
All right, so we'll copy that down and for every category we're taking the sales price times the fee associated with that category and you know that's step one.
Now, step two is sometimes we have to apply the MAX amount and I don't like sometimes.
I want formulas to be able to work all the time not just sometimes.
So, this is eBay right?
These are ebay categories and ebay fees and ebay max amounts.
Well, that you know these items that don't have a max amount they really, truly do have a max amount.
I mean, come on, face it you've shopped eBay before, what's the largest purchase you've ever made on eBay?
There certainly is a max amount like there would never be a fee that's more than 99 99 999.
That would mean that you sold something for not just 99 million pounds but 99 million pounds divided by 11% almost a billion dollars.
I've never seen anything on eBay being sold for a billion dollars.
So, let's just put that max amount in there and then for every category we can assume that there is a max and we treat all the categories the same.
I also want to take a step back and explain why we use the MIN to figure out if we've hit the max or not.
So, here's just a fictitious calculation.
We have numbers here and we have e basing.
Hey, the Max's calculation can be as 750 so what's the answer the logical way that most people would think about this is +IF (B2<A2, B2, A2) if the max is less than the calculation then we use the max otherwise we use the calculation.
I think that makes sense to a lot of people that that's just how we think about it, we look at this and choose one or the other.
Turns out that what these answers here are the exact same answers we would get if we use the MIN of the two items.
So, that one's the same, same, same, same, same, same and what we're doing here is we're setting up kind of a race.
All right, and we want to see who has the lowest score and in this case 685 vs 750 which one is lowest?
If the calculation is lowest that's what wins.
But here where the calculation is 890 versus 750 the 750 which is in the column called max is the fastest score lowest score and so it went in the min.
So, the min is a shorter way to do this calculation and we don't have all of the extra in essence, there's four references here and only two references there, some might argue that that's one reference but it's two sales and that one reference.
So, coming back here we have our calculation and for every single category we're going to look for this MIN of the original calculation comma and then the max for that category so =MIN(VLOOKUP(A2,$E$2:$F$10,2,False)*B2,VLOOKUP(A2,$E$2:$G$10,3,False)) vlookup of I'll have to press f2 there to go back into point mode of that category go over three columns f 4 comma 3 comma false parentheses close the vlookup parentheses to close the min and double-click to shoot that down and you see that some of these items like the 855 times 10% change to $40 because of the MIN.
The other items which never got close to the MAX they stayed exactly the way that they were.
So, by coming back here to this table adding in the really big numbers.
Numbers that are larger than any number that could possibly happen from this calculation we get to use one set of logic instead of having to test for both sets false.
Hey, I want to thank everyone for stopping by.
See you next time for another dueling Excel podcast from MrExcel and ExcelIsFun.
