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. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,517
    Thanks
    7
    Thanked 220 Times in 208 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

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,206
    Thanks
    14
    Thanked 331 Times in 324 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

  5. #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

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

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

    What is the error message?

    Steve

  8. #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

  9. #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!!!

  10. #9
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,517
    Thanks
    7
    Thanked 220 Times in 208 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

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

  12. #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

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

  14. #13
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,111
    Thanks
    39
    Thanked 199 Times in 186 Posts
    Svokozela,

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

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

    zeddy

  16. #15
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,111
    Thanks
    39
    Thanked 199 Times in 186 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
  •