Classify in categories

[Guest]

I would like to classify de persons in categories in my spreatsheet.
I wanted to use de next formula:
=
ALS(EN(17<C14;C14<23);"18-22";ALS(EN(22<C14;C14<26);"23-25";ALS(EN(25<C14;C14<31);"26-30";ALS(EN(30<C14;C14<36);"31-35";ALS(EN(35<C14;C14<41);"36-40";ALS(EN(40<C14;C14<46);"41-45";ALS(EN(45<C14;C14<51);"46-50";ALS(EN(50<C14;C14<56);"51-55";ALS(EN(55<C14;C14<61);"56-60";ALS(EN(60<C14;C14<66);"61-65";"65<"))))))))))
But it'snt possible to use more than 7 functions in one formula, so now I
would like to know if there's an other way to do this.

This is how my spreatsheet looks like and the last colomn is what I want.
Employee Birthday Age Category
0001 06-11-1969 36 36-40
0002 23-11-1966 39 36-40
0003 15-08-1976 30 26-30
0004 27-09-1978 28 26-30
0005 28-10-1969 36 36-40
0006 09-11-1976 29 26-30
0007 11-08-1975 31 31-35
0008 04-12-1963 42 41-45
0009 14-08-1967 39 36-40
0010 27-03-1965 41 41-45
0011 30-11-1975 30 26-30
0012 12-01-1960 46 46-50
0013 16-11-1979 26 26-30
0014 08-11-1976 29 26-30

 

[Phil]

I would like to classify de persons in categories in my spreatsheet.
I wanted to use de next formula:
=
ALS(EN(17<C14;C14<23);"18-22";ALS(EN(22<C14;C14<26);"23-25";ALS(EN(25<C14;C14<31);"26-30";ALS(EN(30<C14;C14<36);"31-35";ALS(EN(35<C14;C14<41);"36-40";ALS(EN(40<C14;C14<46);"41-45";ALS(EN(45<C14;C14<51);"46-50";ALS(EN(50<C14;C14<56);"51-55";ALS(EN(55<C14;C14<61);"56-60";ALS(EN(60<C14;C14<66);"61-65";"65<"))))))))))
But it'snt possible to use more than 7 functions in one formula, so now I
would like to know if there's an other way to do this.

This is how my spreatsheet looks like and the last colomn is what I want.
Employee Birthday Age Category
0001 06-11-1969 36 36-40
0002 23-11-1966 39 36-40
0003 15-08-1976 30 26-30
0004 27-09-1978 28 26-30
0005 28-10-1969 36 36-40
0006 09-11-1976 29 26-30
0007 11-08-1975 31 31-35
0008 04-12-1963 42 41-45
0009 14-08-1967 39 36-40
0010 27-03-1965 41 41-45
0011 30-11-1975 30 26-30
0012 12-01-1960 46 46-50
0013 16-11-1979 26 26-30
0014 08-11-1976 29 26-30

 

[Roger Govier]

Hi Joyce

In cell D2
=IF(RIGHT(C2)>"5",
LEFT(C2)&"6 - "&LEFT(C2)+1&"0",
LEFT(C2)&"1 - "&LEFT(C2)&"5")

Clearly you will need to translate into your own language, and change
the comma (,) separator to your semicolon (

 

[Roger Govier]

Hi Joyce

Apologies, we need an extra IF clause to deal with 30, 40 etc. as the
final digit is less than 5, but we want the previous banding.

Try
=IF(RIGHT(C1)="0",LEFT(C1)-1&"6 - "&LEFT(C1)&"0",
IF(RIGHT(C1)>"5",LEFT(C1)&"6 - "&LEFT(C1)+1&"0",
LEFT(C1)&"1 - "&LEFT(C1)&"5"))

 

[Phil]

Hi Joyce,

I think my reply somehow went missing!

Try using a VLOOKUP formula.

In your case it might look like:

=VLOOKUP(A1 [where you would enter the employee number to
lookup],[Range of the data to look up - which would extend from the
first cell of your set of data to the bottom right corner],Column
number containing the data to be looked up - in your case 4)

so it could be =VLOOKUP(A1,'Sheetname!$A$1:$D$20,4,FALSE)

The good think is that this formula can be used in another workbook
altogether and will still look up the data from your Employee's
workbook even if it's closed

Hope this helps,

Phil