|
commission calculation
I have a spreadsheet set up that calculates my total production volume and is
entered in a cell for Totals. I want to take that total number and calculate commissions using the following formula: if volume 0-500000, multiply by .0045 if 500001-750000, multiply by .0050 if 750001-1250000, multiply by .0055 if 1250001-2000000, multiply by .0060 if 2000000 and above, multiply by .0065 and return the answer to the cell labeled "Commission" -- Rick |
commission calculation
Create a table as below say in Sheet2 A1 to B5 with Volumes & Commission rates:
A B 0 0.0045 500001 0.0050 750001 0.0055 1250001 0.0060 2000001 0.0065 With production volume in A1 of Sheet1 in "Commission" (assuming it is on Sheet1 also) cell put =A1*VLOOKUP(A1,Sheet2!$A$1:$B$5,2,1) Alternative options: =A1*LOOKUP(A1,Sheet2!$A$1:$A$5,Sheet2!$B$1:$B$5) or (but less flexible than using a table): =A1*LOOKUP(A1,{0,500001,750001,1250001,2000001},{0 .0045,0.005,0.0055,0.006,0.0065}) HTH "Rick" wrote: I have a spreadsheet set up that calculates my total production volume and is entered in a cell for Totals. I want to take that total number and calculate commissions using the following formula: if volume 0-500000, multiply by .0045 if 500001-750000, multiply by .0050 if 750001-1250000, multiply by .0055 if 1250001-2000000, multiply by .0060 if 2000000 and above, multiply by .0065 and return the answer to the cell labeled "Commission" -- Rick |
commission calculation
=IF(A302000000,A30*0.0065,IF(A30=1250001,A30*0.0 06,IF(A30=750001,A30*0.0055,IF(A30=500001,A30*0. 005,IF(A30=0, A30*0.0045,"")))))
computed for total in A30, adjust as necessary. "Rick" wrote: I have a spreadsheet set up that calculates my total production volume and is entered in a cell for Totals. I want to take that total number and calculate commissions using the following formula: if volume 0-500000, multiply by .0045 if 500001-750000, multiply by .0050 if 750001-1250000, multiply by .0055 if 1250001-2000000, multiply by .0060 if 2000000 and above, multiply by .0065 and return the answer to the cell labeled "Commission" -- Rick |
commission calculation
=IF(D12=2000000,D12*0.0065,IF(D12=1250001,D12*0. 006,IF(D12=750001,D12*0.0055,IF(D12=500001,D12*0 .005,D12*0.0045))))
or should it be =IF(D12=2000001,D12....................... just curious why only the top range starts at 2000000 -- Greetings from New Zealand "Rick" wrote in message ... I have a spreadsheet set up that calculates my total production volume and is entered in a cell for Totals. I want to take that total number and calculate commissions using the following formula: if volume 0-500000, multiply by .0045 if 500001-750000, multiply by .0050 if 750001-1250000, multiply by .0055 if 1250001-2000000, multiply by .0060 if 2000000 and above, multiply by .0065 and return the answer to the cell labeled "Commission" -- Rick |
commission calculation
Another idea
=LOOKUP(F14/10000,{0,50,75,125,200},{0.0045,0.005,0.0055,0.006 ,0.0065})*f14 -- Don Guillett SalesAid Software "Rick" wrote in message ... I have a spreadsheet set up that calculates my total production volume and is entered in a cell for Totals. I want to take that total number and calculate commissions using the following formula: if volume 0-500000, multiply by .0045 if 500001-750000, multiply by .0050 if 750001-1250000, multiply by .0055 if 1250001-2000000, multiply by .0060 if 2000000 and above, multiply by .0065 and return the answer to the cell labeled "Commission" -- Rick |
commission calculation
Thanks. this was a big help to a novice like me.
-- Rick "Toppers" wrote: Create a table as below say in Sheet2 A1 to B5 with Volumes & Commission rates: A B 0 0.0045 500001 0.0050 750001 0.0055 1250001 0.0060 2000001 0.0065 With production volume in A1 of Sheet1 in "Commission" (assuming it is on Sheet1 also) cell put =A1*VLOOKUP(A1,Sheet2!$A$1:$B$5,2,1) Alternative options: =A1*LOOKUP(A1,Sheet2!$A$1:$A$5,Sheet2!$B$1:$B$5) or (but less flexible than using a table): =A1*LOOKUP(A1,{0,500001,750001,1250001,2000001},{0 .0045,0.005,0.0055,0.006,0.0065}) HTH "Rick" wrote: I have a spreadsheet set up that calculates my total production volume and is entered in a cell for Totals. I want to take that total number and calculate commissions using the following formula: if volume 0-500000, multiply by .0045 if 500001-750000, multiply by .0050 if 750001-1250000, multiply by .0055 if 1250001-2000000, multiply by .0060 if 2000000 and above, multiply by .0065 and return the answer to the cell labeled "Commission" -- Rick |
commission calculation
Thanks again, This was a big help
-- Rick "Bill Kuunders" wrote: =IF(D12=2000000,D12*0.0065,IF(D12=1250001,D12*0. 006,IF(D12=750001,D12*0.0055,IF(D12=500001,D12*0 .005,D12*0.0045)))) or should it be =IF(D12=2000001,D12....................... just curious why only the top range starts at 2000000 -- Greetings from New Zealand "Rick" wrote in message ... I have a spreadsheet set up that calculates my total production volume and is entered in a cell for Totals. I want to take that total number and calculate commissions using the following formula: if volume 0-500000, multiply by .0045 if 500001-750000, multiply by .0050 if 750001-1250000, multiply by .0055 if 1250001-2000000, multiply by .0060 if 2000000 and above, multiply by .0065 and return the answer to the cell labeled "Commission" -- Rick |
commission calculation
Thank you. This was a big help.
-- Rick "BoniM" wrote: =IF(A302000000,A30*0.0065,IF(A30=1250001,A30*0.0 06,IF(A30=750001,A30*0.0055,IF(A30=500001,A30*0. 005,IF(A30=0, A30*0.0045,""))))) computed for total in A30, adjust as necessary. "Rick" wrote: I have a spreadsheet set up that calculates my total production volume and is entered in a cell for Totals. I want to take that total number and calculate commissions using the following formula: if volume 0-500000, multiply by .0045 if 500001-750000, multiply by .0050 if 750001-1250000, multiply by .0055 if 1250001-2000000, multiply by .0060 if 2000000 and above, multiply by .0065 and return the answer to the cell labeled "Commission" -- Rick |
commission calculation
Thanks alot. This was a big help.
-- Rick "Don Guillett" wrote: Another idea =LOOKUP(F14/10000,{0,50,75,125,200},{0.0045,0.005,0.0055,0.006 ,0.0065})*f14 -- Don Guillett SalesAid Software "Rick" wrote in message ... I have a spreadsheet set up that calculates my total production volume and is entered in a cell for Totals. I want to take that total number and calculate commissions using the following formula: if volume 0-500000, multiply by .0045 if 500001-750000, multiply by .0050 if 750001-1250000, multiply by .0055 if 1250001-2000000, multiply by .0060 if 2000000 and above, multiply by .0065 and return the answer to the cell labeled "Commission" -- Rick |
commission calculation
|
| All times are GMT +1. The time now is 02:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com