If statement

[Blake 7]

Hi guys and many thanks for your help in advance

The Problem...

All sales revenue (C14:C25) receives a commission payment ( I have manually entered the commission payable in E14:E25). The amount of commission paid changes as the cumulative sales increase according to the bands seen in (C5:E9). So, £0 to £40,000 receives 2%, >£40,000 and <=£75,000 receives 3% , >£75,000 and <=£110,000 recieves 4% ect

Im am trying to find a formula for (E14:E25) to work out commission payable but am struggling when revenue in a month needs to be attributted to different bands.

Where I have added the manual calculation I have also given a written explanation as to the calculation.

Could anyone help with this? Many thanks

[romperstomper]

Shouldn't your August calc be 1100?

By adding a row above your commission rates table with 0 for both columns, you can use this formula in F15:

=SUMPRODUCT(--(D15>$D$5:$D$9),(D15-$D$5:$D$9),$E$6:$E$10-$E$5:$E$9)-SUM(F$14:F14)

See attached.

[Blake 7]

Hey - perfect, worked a treat and you were right about August!

Many thanks for your time - much appreciated.

All the best

[Vikas_Gautam]

Here is my way....More Independent one..

Please Login or Register  to view this content.

check the attached file..

[Blake 7]

thanks for an alternative solution Vikas