Can you help me please
 

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

Can you help me please
 

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

Can you help me please
 

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

Can you help me please
 

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