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

I am trying to calculate commissions which are paid at varying rates 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 pin the formula down. Any help would be greatly appreciated! Please ask if I haven't explained what I need very well. Thanks in advance!

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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.

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

I also need to calculate commissions which are a set amount within a 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.

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Perfect, thank you so much!