Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question 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
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,833
    Thanks
    7
    Thanked 252 Times in 237 Posts
    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. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    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. #4
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy

    Quote Originally Posted by Paul T View Post
    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. #5
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    The attachment is an invalid link...

    What is the error message?

    Steve

  7. #7
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up thank you

    Quote Originally Posted by Paul T View Post
    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

  8. #8
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Its fine Steve, it worked, thank you so much for your help!!!

  9. #9
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,833
    Thanks
    7
    Thanked 252 Times in 237 Posts
    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

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    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

  11. #11
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quote Originally Posted by sdckapr View Post
    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

  12. #12
    New Lounger
    Join Date
    Jan 2010
    Location
    Lilydale, Victoria, Australia
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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).

  13. #13
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,349
    Thanks
    48
    Thanked 273 Times in 251 Posts
    Svokozela,

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

  14. #14
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,525
    Thanks
    32
    Thanked 180 Times in 174 Posts
    ..or you could also enter
    E2 =IF(MIN(C2,D2)>3,"Bonus","")
    ..and copy down

    zeddy

  15. #15
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,349
    Thanks
    48
    Thanked 273 Times in 251 Posts
    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 LastLast

Posting Permissions

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