HELP? Formula to Figure Split Commissions But Not Retroactive

Posted by Ron on February 04, 2001 10:05 PM

Dave helped but I need it to be split but not retroactive to o....

If sales are: = 53,135

7% from 0-35000 = 2450
8% from 35001-45000 = 800
9% from 45001-53135 = 732

Total Should be = 3982

Thank you for your help........

My wife needed this by tomorrow AM for a Sales Meeting so I apologize for being so persistent but I have read read and read more then trial and errored until I am blue...

Posted by Celia on February 04, 2001 11:24 PM

Function MyComm(Amount As Range)
Application.Volatile
Select Case Amount
Case 0 To 35000
MyComm = Amount * 0.07
Case 35001 To 45000
MyComm = (35000 * 0.07) + _
(Amount - 35000) * 0.08
Case Is > 45001
MyComm = (35000 * 0.07) + _
(10000 * 0.08) + _
(Amount - 45000) * 0.09
End Select
End Function

Celia

Posted by Celia on February 04, 2001 11:26 PM

A bit shorter

Function MyComm(Amount As Range)
Application.Volatile
Select Case Amount
Case 0 To 35000
MyComm = Amount * 0.07
Case 35001 To 45000
MyComm = 2450 + _
(Amount - 35000) * 0.08
Case Is > 45001
MyComm = 3250 + _
(Amount - 45000) * 0.09
End Select
End Function

Posted by Aladin Akyurek on February 05, 2001 2:15 AM

Ron,

You might also use a formula like the one below.

=IF(A1 LessThanEequal 35000,A1*7%,35000*7%)+IF(AND(A1>35000,A1 LT 45000),(45000-A1)*8%,IF(A1>=45000,10000*8%+(A1-45000)*9%,0))

Note. Replace LessThanEqual and LessThan by the appropriate relational sysmbols.

I think you can adjust this easily if I overlooked something.

Cheers.

Aladin