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

Here's one way to do it. Copy this function and put it in a standard
module. To use it, put this in a cell:
=cms(N3,T5)
Hope this helps. James

Function cms(Sales, Profit) As Double
Select Case Sales
Case Is < 7001: cms = 0.1
Case 7001 To 8000: cms = 0.11
Case 8001 To 9200: cms = 0.12
Case 9201 To 10400: cms = 0.125
Case 10401 To 11500: cms = 0.13
Case 11501 To 17000: cms = 0.135
Case 17001 To 20500: cms = 0.14
Case 20501 To 23000: cms = 0.145
Case Is 23000: cms = 0.15
End Select
cms = cms * Profit
End Function


Digital2k wrote:
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

Thank you but when I put =cms(N3,T5) in a cell I get #NAME?
Any thoughts?
Digital2k

"Zone" wrote in message
ups.com...
Here's one way to do it. Copy this function and put it in a standard
module. To use it, put this in a cell:
=cms(N3,T5)
Hope this helps. James

Function cms(Sales, Profit) As Double
Select Case Sales
Case Is < 7001: cms = 0.1
Case 7001 To 8000: cms = 0.11
Case 8001 To 9200: cms = 0.12
Case 9201 To 10400: cms = 0.125
Case 10401 To 11500: cms = 0.13
Case 11501 To 17000: cms = 0.135
Case 17001 To 20500: cms = 0.14
Case 20501 To 23000: cms = 0.145
Case Is 23000: cms = 0.15
End Select
cms = cms * Profit
End Function


Digital2k wrote:
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

Hmm, it works fine for me. Did you put it in a standard module in the
same workbook as your worksheet? That is, did you go to the code edit
window (Alt-F11), choose Insert from the menubar, then Module, and
paste the code in there? What version of Excel are you using? Try
typing the =cms(N3,T5) in the cell again. James

Digital2k wrote:
Thank you but when I put =cms(N3,T5) in a cell I get #NAME?
Any thoughts?
Digital2k

"Zone" wrote in message
ups.com...
Here's one way to do it. Copy this function and put it in a standard
module. To use it, put this in a cell:
=cms(N3,T5)
Hope this helps. James

Function cms(Sales, Profit) As Double
Select Case Sales
Case Is < 7001: cms = 0.1
Case 7001 To 8000: cms = 0.11
Case 8001 To 9200: cms = 0.12
Case 9201 To 10400: cms = 0.125
Case 10401 To 11500: cms = 0.13
Case 11501 To 17000: cms = 0.135
Case 17001 To 20500: cms = 0.14
Case 20501 To 23000: cms = 0.145
Case Is 23000: cms = 0.15
End Select
cms = cms * Profit
End Function


Digital2k wrote:
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