Hello,
I'm trying to create a spreadsheet to track commissions.
I want to track my weekly sales. My week starts Saturday and ends Friday.
Cell N3 is the sum of Column N Row 5-105 for total sales. The tricky part
(for me) is that if cell N3 is between $0- $7000 within one week, my
commission is 10%, here is the breakdown:$0 - $7,000 = 10.0%
$7,001 - $8,000 = 11.0%
$8,001 - $9,200 = 12.0%
$9,201 - $10,400 = 12.5%
$10,401 - $11,500 = 13.0%
$11,501 - $17,000 = 13.5%
$17,001 - $20,500 = 14.0%
$20,501 - $23,000 = 14.5%
$23,001 + = 15.0%
Here is the setup on Row 5: cell N5 has the total sale. Cell T5 has the
gross profit.
N3 has the total sum of sales. cell P5 has the commission.
I want to figure out want my commission is from the gross profit. For
example:
If T5 is $2,000. and N3 = $10,000 within one week, my commission is 12.5% of
$2,000, so Cell P5 should = $250
How can I have this work automatically using the commission chart?
If I can get the right formula I can copy it to the rest of the cells unless
there's a better way using a macro.
I know I'm all over the place but any help would greatly be appreciated.
Thank you,
Digital2k