I need a formula for a tiered commission scale that looks like the below
70=$700, 75=$750, 80=$880, 85=$935, 90=$990, 95=$1045, 100=$1200, 105=$1380, 110=$1580, 115=$1840, 120=$2100, 125=$2188, 130=$2275, 135=$2363, 140=$2450, 145=$2538, 150=$2625, 155=$2713, 160=$2800, 165=$2888, 170=$2975, 175=$3063, 180=$3150.
I would enter the numerical number in column B and in the cell directly beside it in column C the commission would be calculated based on the number you enter into column B. I have tried several "IF" formula's and can't find one that works and I am desperate at this point please help!!
- Follow via:
- RSS
- Email Alert
Question
0
Votes
Answers (1)
0
Votes
VLOOKUP
You want to use the VLOOKUP function. Start by creating two columns somewhere (I'm going to use columns A and B of Sheet2). In column A add your values (70, 75, 80 etc). In column B add the corresponding commission amount (700, 750, 880 etc). This is your lookup table.
Now go back to Sheet1. Enter your actual sales numbers into a column (I'm going with column E, just to be different). In column F insert a formula and select VLOOKUP. You'll need to enter four things:
1) The lookup value. Enter E1 here
2) The lookup table. Enter Sheet2!A1:B23 here.
3) The column offset in your lookup table. You want to select column 2 (I'll explain why below)
4) Range lookup. You'll probably want to set this to true.
What this does is:
* Takes the value (E1)
* Goes and looks for that value in the first column of your lookup table (ie. column A of sheet2)
* When it finds it, it then looks on the same row for the value to return. You chose 2, which means it will return the value from column B of sheet2.
So if you enter 80 into cell E1, the lookup will go and find 80 in Sheet2!A3. It will then return the value from column B - $880
Now let's say you enter 79 into cell E1. If you set range lookup to false the lookup will fail because 79 does not exist in column A of Sheet2. If you set range lookup to false however it will go with the closest match it can find. The closest value in your lookup table is 75, so $750 will be returned.
Now go back to Sheet1. Enter your actual sales numbers into a column (I'm going with column E, just to be different). In column F insert a formula and select VLOOKUP. You'll need to enter four things:
1) The lookup value. Enter E1 here
2) The lookup table. Enter Sheet2!A1:B23 here.
3) The column offset in your lookup table. You want to select column 2 (I'll explain why below)
4) Range lookup. You'll probably want to set this to true.
What this does is:
* Takes the value (E1)
* Goes and looks for that value in the first column of your lookup table (ie. column A of sheet2)
* When it finds it, it then looks on the same row for the value to return. You chose 2, which means it will return the value from column B of sheet2.
So if you enter 80 into cell E1, the lookup will go and find 80 in Sheet2!A3. It will then return the value from column B - $880
Now let's say you enter 79 into cell E1. If you set range lookup to false the lookup will fail because 79 does not exist in column A of Sheet2. If you set range lookup to false however it will go with the closest match it can find. The closest value in your lookup table is 75, so $750 will be returned.
Updated - 1st Mar
