Formula for Commissions
Posted by Ron on February 04, 2001 6:47 PM
I just need a formula for commissions as I have tried and tried and keep getting errors....
If sales are:
0-35,000 = 7%
35,001-45,000 = 8%
45,001 - infinity = 9%
Thank you for your help........
Posted by Dave Hawley on February 04, 2001 6:56 PM
Hi Ron
Here is a custom function that will make life easier. To use it simply push Alt+F11, go to Insert>Module and paste in the code:
Function MyComm(Amount As Range)
Select Case Amount
Case 0 To 35000
MyComm = Amount * 0.7
Case 35001 To 45000
MyComm = Amount * 0.8
Case Is > 45001
MyComm = Amount * 0.9
End Select
End Function
Push Alt+Q to return to excel. Now save.
Now in any cell put:
=MyComm(A1)
Where A1 contains the amount.
Hope this helps
Dave
OzGrid Business Applications
Posted by Ron on February 04, 2001 7:33 PM
Dave:
Thank you so much!!!
I had to change to 0.07 etc but it is not calculating properly for some reason..
1. I calculates the .07 correct but over 35,000 is where it is wrong...
2. IE:
53,135 4,782
40,620 3,250
51,421 4,628
51,822 4,664
20,500 1,435
Thank you!!
Ron
`````````````````````````````````````````````
Posted by Ron on February 04, 2001 7:41 PM
Dave:
I see what it is doing... It is a graduated commission and not retroactive so it is calculating the complete amount x each % once it reaches that plateau....
IE: = 53,135
0-35000 = 2450
35001-45000 = 800
45001-53135 = 732
Total = 3982 and it is calculating it as 4782...
But thanks os much anyway as this will be a great help as I am trying to figure commissions for my wife's EA designers for the past 6 years...
Ron
`````````````````````````````````````````````
Posted by Mark W. on February 05, 2001 7:13 AM
No Need for VBA
Ron, there's really no reason to resort to VBA since
Excel abuilt-in function that can handle this easily.
Assuming that your sales value is in cell A1, you
can calculate your commission using:
=A1*VLOOKUP(A1,{0,0.07;35001,0.08;45001,0.09},2)
Posted by Celia on February 05, 2001 8:10 AM
Re: No Need for VBA
It seems to me that the reason to "resort to VBA" is because it's easier to enter =MyComm(A1).
Celia
Posted by Mark W. on February 05, 2001 9:10 AM
Re: No Need for VBA
Celia, VBA works fine until the user disables macros
to prevent the spread of viruses. Built-in functions
are immune. Customization with VBA also makes the
worksheet less intuitive to the vast body of Excel
users that understand and rely on built-in functions.
Finally, I'm a firm believer in not re-inventing the
wheel.
Posted by Celia on February 05, 2001 3:09 PM
Re: No Need for VBA
Mark
Can't agree. I'm a firm believer in making life easier.
The vast body of Excel users are not going to understand your formula anyway, so it's far from being intuitive.
Wheel re-invention? I don't think so. More like a bit of oiling.
Celia
Posted by Mark W. on February 05, 2001 4:02 PM
Re: No Need for VBA
Well, then let's agree to disagree. I for one have
seen too many people fooling around with VBA before
they ever learn how to use native Excel. I figure...
(except for a limited number of tasks not currently
supported by Excel) if you wanna program then do it
from scratch without the overhead of a spreadsheet
paradigm. If you want reports then use a report writer.
If you're in need of a serious database applications
then learn SQL. In effect... the right tool for the
right job!
Posted by Celia on February 05, 2001 4:21 PM
Re: No Need for VBA
Agree with your first point. Let's not get carried away with this.
