IF FUNCTION with multipe variables

**latruwejelle** · 07-29-2011, 05:18 AM

i'm trying to find a formula for calculating a bonus system.
input:
3 months aug - sept - okt
3 amounts possible: 15 - 30 - 60
15€ is for every sales they make, as from the first, if target is reached for that month
30€ is for every sales they make, as from the first, if target is reached for 2 following months. The third month is 0 if under target or 15 if target is reached.
60€ is for every sales the make, as from the first, if target is reached for 3 following months

Is this possible?

Example is attach

thx,
Jelle

**arthurbr** · 07-29-2011, 05:33 AM

Hi Waregem,

Does Col A represent the sales person?
Is " Plan Sales" the Target and Actual what is already sold?

Cheers from Zaventem ;-)

**latruwejelle** · 07-29-2011, 05:35 AM

correct, correct & correct

waiting for a plane to the sun?

**arthurbr** · 07-29-2011, 05:48 AM

Could you post a couple of examples of calculations, just to be on the right side?

**latruwejelle** · 07-29-2011, 06:14 AM

In attach an example with manual calculations.

If possible, it would be nice to be able to "quick adapt" the values 15 - 30 - 60.
Perhaps if the formule reffers to a cell, this could be possible?

regards
Jelle

**arthurbr** · 07-29-2011, 07:21 AM

Salesline 26 states target reached for december, but it is actually not (166 vs 167)
Is Bonus given anyway? And if yes, which are the rules of the game in this case? Roundup to the next integer?

**latruwejelle** · 07-29-2011, 08:06 AM

bonus is given if percentage is 100%.
It seems harsh not to pay if the difference is only 1...

**arthurbr** · 07-29-2011, 08:16 AM

OK, but which are the rules? Rounding up to the next integer and if 100% give bonus?

**latruwejelle** · 07-29-2011, 08:18 AM

that is correct.

**arthurbr** · 07-29-2011, 08:28 AM

I came up with the file attached

It's not a very nice solution, but it seems to work( except for your last requirement), and it definitely would be unusable for longer periods