A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

STEP MULTIPLY FUNCTION



 
 
Thread Tools Display Modes
  #1  
Old January 29th 08, 03:00 PM posted to microsoft.public.excel.worksheet.functions
Mitch Desai
external usenet poster
 
Posts: 1
Default 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 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
Ads
  #2  
Old January 29th 08, 03:24 PM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
external usenet poster
 
Posts: 2,118
Default STEP MULTIPLY FUNCTION

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




  #3  
Old January 29th 08, 05:05 PM posted to microsoft.public.excel.worksheet.functions
ryguy7272
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

>
>
>
>

  #4  
Old January 29th 08, 06:17 PM posted to microsoft.public.excel.worksheet.functions
Mitch Desai[_2_]
external usenet poster
 
Posts: 1
Default 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 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

> >
> >
> >
> >

  #5  
Old January 29th 08, 08:42 PM posted to microsoft.public.excel.worksheet.functions
ryguy7272
external usenet poster
 
Posts: 2,836
Default 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 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
> > >
> > >
> > >
> > >

  #6  
Old January 30th 08, 07:44 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 22,912
Default 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 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---


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can anyone povide step by step instructions on how to do the follo Daniel Bunt Excel Discussion (Misc queries) 6 January 26th 07 12:58 PM
can't install CD from Excel 2003 step by step djclark New Users to Excel 0 February 9th 06 11:31 PM
Need step by step for entering data into combobox mandy Excel Discussion (Misc queries) 2 December 7th 05 03:37 PM
What is the step-by-step procedure for making a data list? Bobgolfs56 Excel Discussion (Misc queries) 1 April 23rd 05 02:19 PM
I need step by step instructions to create a macro for 10 imbedde. diana Excel Worksheet Functions 3 January 31st 05 01:56 AM


All times are GMT +1. The time now is 06:00 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.