commission calculation

[Rick]

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"

 

[Toppers]

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

 

[BoniM]

=IF(A30>2000000,A30*0.0065,IF(A30>=1250001,A30*0.006,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.

 

[Bill Kuunders]

=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

 

[Don Guillett]

Another idea
=LOOKUP(F14/10000,{0,50,75,125,200},{0.0045,0.005,0.0055,0.006,0.0065})*f14

 

[Rick]

Thanks. this was a big help to a novice like me.

 

[Rick]

Thanks again, This was a big help
--
Rick

=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

 

[Rick]

Thank you. This was a big help.
--
Rick

=IF(A30>2000000,A30*0.0065,IF(A30>=1250001,A30*0.006,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]

Thanks alot. This was a big help.
--
Rick

Another idea
=LOOKUP(F14/10000,{0,50,75,125,200},{0.0045,0.005,0.0055,0.006,0.0065})*f14

 

[LMM300M]

can you send me a copy of your sheet? Thanks LMM
(e-mail address removed)