Using If,And,Or statements

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Reply | Quote

The attachment is an invalid link…

What is the error message?

Steve

Reply | Quote

Its fine Steve, it worked, thank you so much for your help!!!:)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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).

Reply | Quote

Svokozela,

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

Reply | Quote

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

zeddy

Reply | Quote

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

Reply | Quote

Try this one. The example is from the righmost 2 columns.

=IF(S2>=4,(IF(R2>=4,”Yes”,”No”)),”No”)

Also, I was curious why the two-year periods were segregated. If that’s what’s required, then I guess you live with it, but if the idea is to determine bonus eligibility based on two consecutive “4 or better” years, the attached spreadsheet will get you there. Also applied some conditional formatting to highlight the winners and losers (and non-players, by default!).

Have fun!
-Loren37611-copy-1a

Reply | Quote