MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.