Formula help to calculate stepped commission percentage in a range

The title might not make perfect sense so I will try to explain what I am trying to achieve in my formula. Commission is paid at 20% on the first 20K of invoicing. Commission is paid at 25% on all invoicing between 20k - 30k and commission is paid at 40% on all invoicing above 30k.

So for example, if 40k is invoiced:

First 20k at 20% - 4k commission
20k-30k at 25% - 2.5k commission
30k+ at 40% - 4k commission

Total commission - 10.5k

I can quite easily create a formula on each line to deal with the above. Where I am getting stuck is where for example only 15k is invoiced so the only commission to be paid is 15k at 20% - 3k commission.

I "think" I need a formula on each line that follows the following logic but I'm not sure which structure of formula i should use:

Line 1. If the figure entered into the "Invoiced" field is greater than 20k then return 20k * 20%, if not greater than 20k then multiply the total figure in the Invoiced field by 20%
Line 2. If the figure entered into the Invoiced field is greater than 20k then return 25% of the difference between 20k and 30k and ignore everything above 30k, else return 0
Line 3. If the figure entered into the Invoiced field is greater than 30k then return 40% of everything above 30k, else return 0.

Line 4. Sum(Line1:Line3)

Any guidance on the structure/formula functions I should use and experiment with would be greatly appreciated.

Originally Posted by Richard Buttrey

Maybe put it all in one cell where the invoiced value is in A1

Formula:

Please Login or Register  to view this content.

Hi Richard, I have achieved my desired result by separating out your formula to 3 separate formulas. Thank you so much!

Originally Posted by FlameRetired

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

That's done, thanks.