Sales Commission
 

To whom it may concern

I am having problems calculating my sales commisson spreadsheet. I
want a variable rate which is:

0-500 = 6%
501 - 4000 = 10%
4001+ = 12%

This is the easy part, however this is ongoin on a monthly basis and if
a sales guys go over the threshold i want to pay them only on the amount
over that threshold

ie a sales guy bills 400 in march and in april he does a further 200,
therefore going over the 500 threshold, can excel work out that the
commission due is 100 @ 6% and 100 @10% therefore making his commision
16.

i am really struggling

Make Thanks in anticipation

Tim


--
Tim Mears
------------------------------------------------------------------------
Tim Mears's Profile: http://www.excelforum.com/member.php...o&userid=34500
View this thread: http://www.excelforum.com/showthread...hreadid=542674

Sales Commission
 

To get an exact answer more details would probably be needed, but i
you're just looking for a hint in the right direction this should ge
you started:
=CHOOSE(IF((AND(A1=0,A1<=500)),1,IF((AND(A1500,A 1<=4000)),2,IF((AND(A14000)),3,"Negativ
Value"))),6%,10%,12%)

Tim Mears Wrote:
To whom it may concern

I am having problems calculating my sales commisson spreadsheet.
want a variable rate which is:

0-500 = 6%
501 - 4000 = 10%
4001+ = 12%

This is the easy part, however this is ongoin on a monthly basis and i
a sales guys go over the threshold i want to pay them only on the amoun
over that threshold

ie a sales guy bills 400 in march and in april he does a further 200
therefore going over the 500 threshold, can excel work out that th
commission due is 100 @ 6% and 100 @10% therefore making his commisio
16.

i am really struggling

Make Thanks in anticipation

Ti

--
Ikaabo
-----------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...fo&userid=3337
View this thread: http://www.excelforum.com/showthread.php?threadid=54267

Sales Commission
 

Many Thanks for your response

I have the formula

=IF(N44000,"0.12",IF(N4500,"0.1",IF(N4<500,"0.06 ","0.06")))

To calculate the sales percentage to pay, this give me the correct
figure however if the next month i put in a figure which takes it over
a threshold it calcualtes the new rate ie 10% however calculates 10% of
the whole monthly figure and not just the amount over that threshold!!
for example

Month 1 - £300 therfere 6% of £300 = £18

Month 2 - a further £300
This takes it over the threshold of 500 therefore 10% however i only
want to pay the 10% on £100 (£600 - threshold £500 = £100) and 6% on
the £200 therefore total commission of £22

Many Thanks i am lost here a bit out of my depth

Tim


--
Tim Mears
------------------------------------------------------------------------
Tim Mears's Profile: http://www.excelforum.com/member.php...o&userid=34500
View this thread: http://www.excelforum.com/showthread...hreadid=542674

Sales Commission
 

Take a look he

http://www.mcgimpsey.com/excel/variablerate.html

In article ,
Tim Mears
wrote:

Many Thanks for your response

I have the formula

=IF(N44000,"0.12",IF(N4500,"0.1",IF(N4<500,"0.06 ","0.06")))

To calculate the sales percentage to pay, this give me the correct
figure however if the next month i put in a figure which takes it over
a threshold it calcualtes the new rate ie 10% however calculates 10% of
the whole monthly figure and not just the amount over that threshold!!
for example

Month 1 - £300 therfere 6% of £300 = £18

Month 2 - a further £300
This takes it over the threshold of 500 therefore 10% however i only
want to pay the 10% on £100 (£600 - threshold £500 = £100) and 6% on
the £200 therefore total commission of £22

Many Thanks i am lost here a bit out of my depth

Tim