I am trying to create a real time bonus calculator that I can use that builds bonus per sale. Here are the rules: I'm selling 2 products, call them Product A and Product B

I have a target of 100 units of Product A and 50 units of Product B. Both products cost £10 each.

My regular bonus for selling Product A is 4% of the total sales and for Product B it is 8%.

I must reach at 60% sales of my targets for BOTH Product A and Product B to release any bonus.

When I achieve 60% in both products, my bonus up to 80% for each will be 0.5 of my regular bonus, i.e 2% and 4%

When I achieve 80% upwards in either, my bonus is 1.O of my regular bonus for the Product I am over 80% or both. i.e

Product A is 70% Product B is 90% - Product A paid at 0.5, Product B at 1.0.

Product A is at 80% Product B is at 110%, both are paid at 1.0

If both Product A and Product B achieve 100% of target, then they will be paid as follows:

Up to 110% 1.5 times regualr bonus (6%/12%)

Up to 120% 2 times

Up to 150% 2.5 times

Over 150% 3 timesSo Product A could be at 102% and Product B at 143% - Product A paid at 6% (1.5 times) and Product B at 20% (2.5 times).

Is there a single formula that can capture these 'What If scenarios? If not how do I do it?

Many Thanks

I came up with a way to calculate the bonuses, but not in a single formula. It may be possible to do it in a single formula, but the fact that you've got so many variables, it makes my head hurt to even think about it. What I did was create a table where you can put your target values, your bonus values, your bonus multipliers, etc.

I then created 2 formulae, one for Product A and one for Product B, which are on dependent on the percentage of each product sold. They will calculate the bonus multiplier for each product separately, but take into account the percentage sold of the other product.

I believe I was able to duplicate the results of the examples you gave.

One of the advantages of using a table is that you can change the targets or the bonus percentages or anything like that, and leave the formula alone.

I will post one of the formulae in this response, but it won't do you any good unless you see the layout of the tables. As much as I hate to post solutions off-list, it would be impossible to try and describe the layout of the spreadsheet in this forum.

If you'll PM me an email address, I'll send you the spreadsheet that uses the formula below:

=IF(AND(F4/B4>=0.6,G4/C4>=0.6),IF(AND(F4/B4>=1,G4/C4>=1),VLOOKUP(F4/B4,A10:C13,2,1),IF(F4/B4>=0.8,B9,IF(F4/B4>=0.6,B8,""))),0)

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History