Nested IF Function Help 1

[Maillme]

Hi there,

I have a cell on my spreadsheet that has the year of birth, and based on the below - I need it to work out the category:

Under 10 born in 1999 or later
Under 12 born in 1997 or later
Youth born 1993 to 1996
Youth - Under 14 born 1995 or 1996
Junior born 1991 or 1992
Senior born 1990 or earlier
Under 23 born 1987 to 1990
Veteran born 1968 or earlier
Vet 40 born 1964 to 1968
Vet 45 born 1959 to 1963
Vet 50 born 1954 to 1958
Vet 55 born 1949 to 1953
Vet 60 born 1944 to 1948
Vet 65 born 1939 to 1943
Vet 70+ born 1938 or earlier
Women born 1992 or earlier*

So, some examples

- If I enter 1997 into A1, I would like A2 to display "Under 12"
- If I enter 1961 into A1, I would like A2 to display "Vet 45"

I've tried all sorts of IF statements, but my very limited knowledge is getting me no where and leading to too many IF arguments!

many thanks for any help you can give,
Neil

 

[bluedragon2]

put in C115 -
[tt]
1938 Vet 70+
1939 Vet 65
1944 Vet 60
1949 Vet 55
1954 Vet 50
1959 Vet 45
1964 Vet 40
1968 Veteran
1987 Under 23
1990 Senior
1991 Junior
1993 Youth
1995 Youth - Under 14
1997 Under 12
1999 Under 10
[/tt]

and in A2 put: =VLOOKUP(A1,C115,2)

Try entering a date in A1...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Maillme]

Worked a treat! thank you!!!

Neil

 

[bluedragon2]

May want to change 1938 to 0...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

...and I would hope that you have a formula something like this to calculate the YEAR values...
[tt]
B C D
Vet 70+ 70 =YEAR(TODAY()-C2*365.25)
Vet 65 69
Vet 60 64
Vet 55 59
Vet 50 54
Vet 45 49
Vet 40 44
Veteran 40
Under 23 21
Senior 18
Junior 17
Youth 15
Youth - Under 14 13
Under 12 11
Under 10 9
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Maillme]

Skipvought,

What exactly do you mean by that?

Also, I;ve noticed it maybe doesnt work as great as i had hoped

If i put 1946 in, i would expect Vet 60 for example - but i just get 'NA' ???

many thanks for all help,
neil

 

[SkipVought]

"I would hope that you have a formula something like this to calculate the YEAR values"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Maillme]

Yea, but why do i need to calculate the year values??

many thanks,
Neil

 

[SkipVought]

cuz, every year, the values change!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}