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

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

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

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

Please see attached spreadsheet.

Originally Posted by Pete_UK

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

Another way:

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

Thank you so muhc. This works perfect :-)