+ Reply to Thread
Results 1 to 9 of 9

IF and AND Functions

  1. #1
    Registered User
    Join Date
    08-25-2010
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    66

    IF and AND Functions

    Hi

    I need help with a IF(AND Formula to Group Seniority together?

    Length of service is = 7 years, 10 months

    Seniority Group
    less than 1 year = <1
    More than 1 year to equal to 3 years = 1-3
    More than 3 years to equal to 5 years = 3-5
    More than 5 years to equal to 10 years = 5-10
    More than 10 years = >10

    =IF(N2<1,"<1",IF(AND(N2>1,N2<=3),"1-3",IF(AND(N2>3,N2<=5),"3-5",IF(AND(N2>5,N2<=10),IF(AND(N2>10),">10")))))
    (the result came back us "false" - not sure why? )

    Thanks
    S

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,930

    Re: IF and AND Functions

    Try it like this:

    =IF(N2<1,"<1",IF(N2<=3,"1-3",IF(N2<=5,"3-5",IF(N2<=10,"5-10",">10"))))

    This assumes that N2 contains a number, but if it contains 7 years, 10 months then you have text values and not numeric, so you will need a different approach.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    08-25-2010
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    66

    Re: IF and AND Functions

    Do you know what would be the correct formula, yes it is a text not numeric. thanks

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,930

    Re: IF and AND Functions

    You need to give some further examples of your data, so we can see the variations that can occur. For example, if the length of service is 9 months, would this be shown as 0 years, 9 months or as just 9 months, and would 1 year and 2 months be shown as 1 year, 2 months or as 1 years, 2 months?

    Attach a sample Excel workbook showing some more examples, as described in the yellow banner at the top of the screen.

    Pete

  5. #5
    Registered User
    Join Date
    08-25-2010
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    66
    Please see attached spreadsheet.
    Quote Originally Posted by Pete_UK View Post
    You need to give some further examples of your data, so we can see the variations that can occur. For example, if the length of service is 9 months, would this be shown as 0 years, 9 months or as just 9 months, and would 1 year and 2 months be shown as 1 year, 2 months or as 1 years, 2 months?

    Attach a sample Excel workbook showing some more examples, as described in the yellow banner at the top of the screen.

    Pete
    Attached Files Attached Files

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.54 for Mac (home)
    Posts
    7,369

    Re: IF and AND Functions

    you have a date of hire and a month column, you are using a datedif formula in col D, why don't you use that for your seniority column like so...
    =IF(DATEDIF(C2,B2,"Y")<1,"<1",IF(DATEDIF(C2,B2,"Y")<=3,"1-3",IF(DATEDIF(C2,B2,"Y")<=5,"3-5",IF(DATEDIF(C2,B2,"Y")<=10,"5-10",">10"))))
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  7. #7
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,107

    Re: IF and AND Functions

    Another way:

    =LOOKUP(DATEDIF(C2,B2,"m"),{0,12,37,61,121},{"<1","1-3","3-5","5-10",">10"})

  8. #8
    Registered User
    Join Date
    08-25-2010
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    66

    Re: IF and AND Functions

    Thank you so muhc. This works perfect :-)

  9. #9
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.54 for Mac (home)
    Posts
    7,369

    Re: IF and AND Functions

    Your welcome AND thank you for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 03-17-2015, 01:35 AM
  2. VBA – User Designed Functions (UDF) - Renaming array functions
    By hbsonly in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-03-2013, 02:00 PM
  3. Which Excel functions work in user-defined functions ?
    By RogeratCCCC in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 04-28-2012, 06:47 PM
  4. Replies: 1
    Last Post: 02-10-2012, 05:27 PM
  5. Replies: 0
    Last Post: 11-15-2007, 05:24 AM
  6. [SOLVED] efficiency: database functions vs. math functions vs. array formula
    By nickname in forum Excel General
    Replies: 2
    Last Post: 07-13-2006, 11:30 PM
  7. [SOLVED] Conversion from Spreadsheet Toolkit functions (ESSV....) to EssBase API functions
    By sujay in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-05-2006, 05:20 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1