Home 
Search 
Today's Posts 
#1




STEP MULTIPLY FUNCTION
I am trying to create a function for calculation of commissions. We have a
step up calculation we are using this year, ex revenue lower than 2mill will be 6.5% of rev, greater than 2mill3mill will be 8% and greater than 3 mill will be 10.5% of revenue. I would like a function that will automatically calculate monthly for whatever revenue I give it. Can anyone help me with a function that will achieve this for numbers such as 2100000 and 3100000 where the difference after the threshold of 2000000 and 3000000 will multiply by the new percentages (for the 3100000 example i need 2000000*6.5%+1000000*8%+100000*10.5%). Thanks 
#3




STEP MULTIPLY FUNCTION
I think this will do what you want:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A1:A10")) Is Nothing Then If IsNumeric(Target) Then Application.EnableEvents = False If Target.Value < 2000000 Then Target.Value = (Target.Value * 0.065) End If If Target.Value 3000000 Then Target.Value = (Target.Value * 0.105) End If If Target.Value <= 3000000 Then If Target.Value = 2000000 Then Target.Value = (Target.Value * 0.085) End If End If Application.EnableEvents = True End If End If End Sub This is a Private Sub, which means it is 'Event Code'. You must rightclick on the sheet that you want to run it from, click on 'View Code', and paste this code in the window that opens. Regards, Ryan  RyGuy "Ron Coderre" wrote: If I understand correctly: All revenue is calculated at 6.5% The revenue from $2M to $3M is calculated at an incremental 1.5% The revenue from $3M+ is calculated at an additional 2.5% If that's true... Try this: With A1 containing a revenue amount This formula calculates the commission: B1: =SUM((A1/10^6={0,2,3})*(A1/10^6{0,2,3})*10^6*({6.5,1.5,2.5}/100)) Is that something you can work with? Post back if you have more questions.  Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Mitch Desai" <Mitch wrote in message ... I am trying to create a function for calculation of commissions. We have a step up calculation we are using this year, ex revenue lower than 2mill will be 6.5% of rev, greater than 2mill3mill will be 8% and greater than 3 mill will be 10.5% of revenue. I would like a function that will automatically calculate monthly for whatever revenue I give it. Can anyone help me with a function that will achieve this for numbers such as 2100000 and 3100000 where the difference after the threshold of 2000000 and 3000000 will multiply by the new percentages (for the 3100000 example i need 2000000*6.5%+1000000*8%+100000*10.5%). Thanks 
#4




STEP MULTIPLY FUNCTION
Probably stupid question but how do I run this to a target cell?
"ryguy7272" wrote: I think this will do what you want: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A1:A10")) Is Nothing Then If IsNumeric(Target) Then Application.EnableEvents = False If Target.Value < 2000000 Then Target.Value = (Target.Value * 0.065) End If If Target.Value 3000000 Then Target.Value = (Target.Value * 0.105) End If If Target.Value <= 3000000 Then If Target.Value = 2000000 Then Target.Value = (Target.Value * 0.085) End If End If Application.EnableEvents = True End If End If End Sub This is a Private Sub, which means it is 'Event Code'. You must rightclick on the sheet that you want to run it from, click on 'View Code', and paste this code in the window that opens. Regards, Ryan  RyGuy "Ron Coderre" wrote: If I understand correctly: All revenue is calculated at 6.5% The revenue from $2M to $3M is calculated at an incremental 1.5% The revenue from $3M+ is calculated at an additional 2.5% If that's true... Try this: With A1 containing a revenue amount This formula calculates the commission: B1: =SUM((A1/10^6={0,2,3})*(A1/10^6{0,2,3})*10^6*({6.5,1.5,2.5}/100)) Is that something you can work with? Post back if you have more questions.  Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Mitch Desai" <Mitch wrote in message ... I am trying to create a function for calculation of commissions. We have a step up calculation we are using this year, ex revenue lower than 2mill will be 6.5% of rev, greater than 2mill3mill will be 8% and greater than 3 mill will be 10.5% of revenue. I would like a function that will automatically calculate monthly for whatever revenue I give it. Can anyone help me with a function that will achieve this for numbers such as 2100000 and 3100000 where the difference after the threshold of 2000000 and 3000000 will multiply by the new percentages (for the 3100000 example i need 2000000*6.5%+1000000*8%+100000*10.5%). Thanks 
#5




STEP MULTIPLY FUNCTION
Notice the range: Range("A1:A10")
Change this to suit your needs. Regards, Ryan  RyGuy "Mitch Desai" wrote: Probably stupid question but how do I run this to a target cell? "ryguy7272" wrote: I think this will do what you want: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A1:A10")) Is Nothing Then If IsNumeric(Target) Then Application.EnableEvents = False If Target.Value < 2000000 Then Target.Value = (Target.Value * 0.065) End If If Target.Value 3000000 Then Target.Value = (Target.Value * 0.105) End If If Target.Value <= 3000000 Then If Target.Value = 2000000 Then Target.Value = (Target.Value * 0.085) End If End If Application.EnableEvents = True End If End If End Sub This is a Private Sub, which means it is 'Event Code'. You must rightclick on the sheet that you want to run it from, click on 'View Code', and paste this code in the window that opens. Regards, Ryan  RyGuy "Ron Coderre" wrote: If I understand correctly: All revenue is calculated at 6.5% The revenue from $2M to $3M is calculated at an incremental 1.5% The revenue from $3M+ is calculated at an additional 2.5% If that's true... Try this: With A1 containing a revenue amount This formula calculates the commission: B1: =SUM((A1/10^6={0,2,3})*(A1/10^6{0,2,3})*10^6*({6.5,1.5,2.5}/100)) Is that something you can work with? Post back if you have more questions.  Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Mitch Desai" <Mitch wrote in message ... I am trying to create a function for calculation of commissions. We have a step up calculation we are using this year, ex revenue lower than 2mill will be 6.5% of rev, greater than 2mill3mill will be 8% and greater than 3 mill will be 10.5% of revenue. I would like a function that will automatically calculate monthly for whatever revenue I give it. Can anyone help me with a function that will achieve this for numbers such as 2100000 and 3100000 where the difference after the threshold of 2000000 and 3000000 will multiply by the new percentages (for the 3100000 example i need 2000000*6.5%+1000000*8%+100000*10.5%). Thanks 
#6




STEP MULTIPLY FUNCTION
Ryan
You are correct that this is event code. But.......being a Private Sub does not necessarily mean it is "Event Code" Private Optional. Indicates that the Sub procedure is accessible only to other procedures in the module where it is declared. See more in VBA Help "Sub statement" Gord Dibben MS Excel MVP This is a Private Sub, which means it is 'Event Code'. You must rightclick on the sheet that you want to run it from, click on 'View Code', and paste this code in the window that opens. Regards, Ryan 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Can anyone povide step by step instructions on how to do the follo  Excel Discussion (Misc queries)  
can't install CD from Excel 2003 step by step  New Users to Excel  
Need step by step for entering data into combobox  Excel Discussion (Misc queries)  
What is the stepbystep procedure for making a data list?  Excel Discussion (Misc queries)  
I need step by step instructions to create a macro for 10 imbedde.  Excel Worksheet Functions 