Bonus Calculation

[ChristianJ]

URGENT!

Hi guys,

I need help with a simple question, but it is urgent as I need to sign a contract with a new colleague in 1 hour!

I need to calculate a bonus for a colleague where the bonus will gradually increase when the colleague generates more turnover (sales) like this:

Turnover (sales) achieved in the range from 100,000USD till 500,000USD the bonus to my colleague should be 1% of the turnover/sales
Turnover (sales) achieved in the range from 500,000USD till 1,000,000USD the bonus to my colleague should be 1,5% of the turnover/sales
Turnover (sales) achieved above 1,000,000USD the bonus to my colleague should be 2% of the turnover/sales

Example:

If the turnover over the full year is 2,000,000 it is NOT a matter of calculation 2% of 2,000,000USD. It is a matter of automatically calculation 1% of the turnover from 100,000-500,000 and then 1,5% of the turnover from 500,000-1,000,000 and then 2% of everything above.

Can you please insert the formula in CELL B14 in the attached?

THANKS!

[Glenn Kennedy]

In B14:

=SUMPRODUCT(--(B12>{0,100000,500000,1000000}),--(B12-{0,100000,500000,1000000}),--({0,0.01,0.005,0.005}))

[ChristianJ]

Thanks. The amounts I have inserted 100,000USD, 500,000USD etc. are examples. I will change the numbers, I can't put it here online. I can see you have inserted those amounts directly in the formula but they should collect the data from the cells instead (B6, C6, B7 etc.):

Udklip.PNG

[Glenn Kennedy]

See file attached.

[ChristianJ]

But I can't change the bonus rates? I don't understand the "difference" columns.

[Glenn Kennedy]

See sheet again. difference (now done by formula) is the difference between the current level's rate and the previous level's rate.

[Phuocam]

Or try:

=SUMPRODUCT(--(B12>B7:B9),--(B12-B7:B9),C7:C9-C6:C8)

[ChristianJ]

Or try:

=SUMPRODUCT(--(B12>B7:B9),--(B12-B7:B9),C7:C9-C6:C8)

This doesn't work

[ChristianJ]

See sheet again. difference (now done by formula) is the difference between the current level's rate and the previous level's rate.

Don't understand why you need a cell with the difference in percentage. The formula should be able to detect the percentage and the corresponding turnover range automatically.

[ChristianJ]

It's like this, but I need to have a 3rd option (instead of just 2 options as this file has)....

[Glenn Kennedy]

Don't understand why you need a cell with the difference in percentage. The formula should be able to detect the percentage and the corresponding turnover range automatically.

The way I did it is the most efficient way of performing the calculation. is there anything wrong with the results? It could (I suppose) be done with a series of nested IF functions, but it would be very messy.