1. ## Using If,And,Or statements

Hi

Im battling with If statement, employees are supposed to get a bonus if they get 4 points and above for two years, if they get 3 and below, they don't get bonus. e.g in C2 employee has 3,77 and D2 has 3 scoring , he will not get a bonus. They get a bonus every 2years but they must get a scoring of 4 or above for the two years. They wont get a bonus if they score 4 or above for 1 year. Please help.

Regards

2. In E2 I'd use something like this: =if((C2+D2)/2>=4,"Bonus","")
Is the bonus only calculated over a specific 2 year period or every year for the preceding 2 years? If the latter you need to move the bonus column to a new row.

cheers, Paul

3. I would use something like:
=IF(AND(C2>=4,D2>=4),"Bonus","")

PaulT's will not always give the right results. For example: a 3 and a 5 will indicate a bonus...

Steve

4. Originally Posted by Paul T
In E2 I'd use something like this: =if((C2+D2)/2>=4,"Bonus","")
Is the bonus only calculated over a specific 2 year period or every year for the preceding 2 years? If the latter you need to move the bonus column to a new row.

cheers, Paul
Hi Paul

Bonus is calculated over a 2year period. One has to get 4 and above to get bonus for both years. if they get a 4 for 1 year and the other 3 for second year then they don't get it. I tried both formulas from you and Steve but it is giving me error message.

See attachment

Thanks
Sharon

5. The attachment is an invalid link...

What is the error message?

Steve

6. ## thank you

Originally Posted by Paul T
In E2 I'd use something like this: =if((C2+D2)/2>=4,"Bonus","")
Is the bonus only calculated over a specific 2 year period or every year for the preceding 2 years? If the latter you need to move the bonus column to a new row.

cheers, Paul
It worked, thank you so much

7. Its fine Steve, it worked, thank you so much for your help!!!

8. Seems unfair to penalize someone who is improving by not giving them a bonus, e.g. year 1 = 3, year 2 = 4, year 3 = 5, but no bonus in year 3.

cheers, Paul

9. As I understand it, If year 2 was 4 and year 3 was 5 they would get a bonus since both are >=4. They would not in Year2 if year 1 was 3 and year2 was 4.. nor would they if year1 was 3 and year2 was 5. It is not the average performance that seems to warrant the bonus but consistently meeting the minimum standards of performance.

Steve

10. Originally Posted by sdckapr
As I understand it, If year 2 was 4 and year 3 was 5 they would get a bonus since both are >=4. They would not in Year2 if year 1 was 3 and year2 was 4.. nor would they if year1 was 3 and year2 was 5. It is not the average performance that seems to warrant the bonus but consistently meeting the minimum standards of performance.

Steve
that is correct, they will get a bonus as long as they get above 4 for 2 consecutive years. if 2013 is 3, 2014 is 4 and 2015 is 5, then they will get a bonus in 2015. we are busy analysing all those people who didn't get a bonus from 2005 till 2013.

Thanks Steve

11. You need to take notice of what sdckapr (Steve) is saying. Paul's equation will give wrong results (although a 3 and a 5 seems worthy of a bonus, as much as 4 and 4 is).

12. Svokozela,

You could also enter in E2 =IF(MIN(C2,D2)>=4,"Bonus","") and copy down

13. ..or you could also enter
E2 =IF(MIN(C2,D2)>3,"Bonus","")
..and copy down

zeddy

14. Zeddy,

You may not have had a chance to view the OP's file but he uses 2 place decimals. His example uses the values 3.77 that would make your tweak invalid.

Maud

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•