View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ryguy7272 ryguy7272 is offline
external usenet poster
 
Posts: 2,836
Default 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 right-click
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 2mill-3mill 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