Help calculating scaled commissions please

[shrimpy80]

Hi, this is my first time posting and I would really appreciate som
help. I am quite a basic excel user and am really struggling to creat
the formula I need!

I am trying to calculate commissions which are paid at varying rate
depending on the number of sales.

If the number of sales is less than four, £0 is payable.
If the number of sales is 4 - 5, they are all payable at £4 each
If the number of sales is 6 - 9, they are all payable at £6 each
If the number of sales is 10 or more, they are all payable at £10 each
(The number of sales is totalled in cell H25)

I have been going round in circles for hours and can't quite seem to pi
the formula down. Any help would be greatly appreciated! Please ask if
haven't explained what I need very well. Thanks in advance

 

[Claus Busch]

Hi,

Am Tue, 10 Sep 2013 17:17:06 +0100 schrieb shrimpy80:

If the number of sales is less than four, £0 is payable.
If the number of sales is 4 - 5, they are all payable at £4 each
If the number of sales is 6 - 9, they are all payable at £6 each
If the number of sales is 10 or more, they are all payable at £10 each
(The number of sales is totalled in cell H25)

try:
=H25*VLOOKUP(H25,{0,0;4,4;6,6;10,10},2,1)
modify the separators to your system


Regards
Claus B.

 

[shrimpy80]

Hi,

Am Tue, 10 Sep 2013 17:17:06 +0100 schrieb shrimpy80:
-

try:
=H25*VLOOKUP(H25,{0,0;4,4;6,6;10,10},2,1)
modify the separators to your system


Regards
Claus B.

Thank you - that has worked perfectly and I have learnt something new
Sorry for the delay in replying, I am truly very grateful for your help

 

[shrimpy80]

Ok - sorry to post again but I have one final question.

I also need to calculate commissions which are a set amount within
given banding, i.e. don't use a multiplier, so:

Less than 150 = 0
Between 150 and 199 = £85 payable
Between 200 and 259 = £270 payable
More than 260 = £400 payable

Is there a formula I could use to automatically show the correct total
Thanks again for your help with this

 

[Claus Busch]

Hi,

Am Mon, 23 Sep 2013 12:17:41 +0100 schrieb shrimpy80:

Less than 150 = 0
Between 150 and 199 = £85 payable
Between 200 and 259 = £270 payable
More than 260 = £400 payable

try:
=VLOOKUP(A1,{0,0;150,85;200,270;260,400},2,1)

Regards
Claus B.

 

[shrimpy80]

Perfect, thank you so much