Michael asks how to calculate a sliding commission rate. Episode 757 shows two approaches, both of which use the sorted version of VLOOKUP.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question is sent in by Michael.
Michael was trying to calculate a sliding commission and you know, he said, you know if you sell over $2,000 you get a certain percentage.
If you sell over $4,000 you get another percentage.
and I wasn't sure from his question if it was a situation where once I sold four thousand and ten dollars, if I got three percent of that entire four thousand or if I only got two percent of everything up to four thousand and then how to calculate it over.
So, assuming we can go with a simpler version.
I set up a lookup table over here, in the lookup table has to be sorted in ascending sequence.
In this example, basically I said, hey! If your sale is zero dollars you're getting one percent, and you're going to keep getting one percent until your sale hits $1000, then you get one and a half percent.
You continue to get one and a half percent until you get to a sale, that's $2,000.
Then you get 1.75 percent and so on down the line.
So, then over here in column A, I have some sales numbers here the 4613.
My formula says, we're going to take A2.
So, the sales in A2 and then do a vlookup.
Go find A2 within lookup table H2 to I7 and when it finds the lookup value, or a number just lower than the lookup values.
In this case, it's going to find the 4000.
It's going to return the second column, which is 3 percent.
So 4613 times 3 percent is 13839.
But down here where they only sold 1719 that $25 is one and a half percent of the 1719.
So, this is the version of the vlookup that does not have comma false at the end, you see that I don't have that fourth argument with the comma false, that's the way I usually use vlookup.
It has to find an exact value but here it can find the nearest value, and it works just fine.
Now, what if you have the other kind of commission plan, where we pay 1% of the first thousand and then one and a half percent of everything from a thousand and one up to 1500.
Well, then in that case the table has to be a little bit different.
We have the same amounts and then calculate for example, this 10 is 1% of the first thousand, plus 1 and 1/2 percent of everything over a thousand.
Now, my formula becomes much more complicated because I have to do well, actually three different vlookup.
To get the base amount plus the percentage times the amount over which that percentage applies.
So, either way it would work.
The trick in both of them though, is to use this sorted version a vlookup.
Well, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today's question is sent in by Michael.
Michael was trying to calculate a sliding commission and you know, he said, you know if you sell over $2,000 you get a certain percentage.
If you sell over $4,000 you get another percentage.
and I wasn't sure from his question if it was a situation where once I sold four thousand and ten dollars, if I got three percent of that entire four thousand or if I only got two percent of everything up to four thousand and then how to calculate it over.
So, assuming we can go with a simpler version.
I set up a lookup table over here, in the lookup table has to be sorted in ascending sequence.
In this example, basically I said, hey! If your sale is zero dollars you're getting one percent, and you're going to keep getting one percent until your sale hits $1000, then you get one and a half percent.
You continue to get one and a half percent until you get to a sale, that's $2,000.
Then you get 1.75 percent and so on down the line.
So, then over here in column A, I have some sales numbers here the 4613.
My formula says, we're going to take A2.
So, the sales in A2 and then do a vlookup.
Go find A2 within lookup table H2 to I7 and when it finds the lookup value, or a number just lower than the lookup values.
In this case, it's going to find the 4000.
It's going to return the second column, which is 3 percent.
So 4613 times 3 percent is 13839.
But down here where they only sold 1719 that $25 is one and a half percent of the 1719.
So, this is the version of the vlookup that does not have comma false at the end, you see that I don't have that fourth argument with the comma false, that's the way I usually use vlookup.
It has to find an exact value but here it can find the nearest value, and it works just fine.
Now, what if you have the other kind of commission plan, where we pay 1% of the first thousand and then one and a half percent of everything from a thousand and one up to 1500.
Well, then in that case the table has to be a little bit different.
We have the same amounts and then calculate for example, this 10 is 1% of the first thousand, plus 1 and 1/2 percent of everything over a thousand.
Now, my formula becomes much more complicated because I have to do well, actually three different vlookup.
To get the base amount plus the percentage times the amount over which that percentage applies.
So, either way it would work.
The trick in both of them though, is to use this sorted version a vlookup.
Well, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
