Need formula to assign age categories for marathon

[bezzinajohn]

Hi

I need a formula which will assign an age category depending on the age of the runner.

Example. if the runner is between 18 and 29years old he will be placed in the "Open" category. If he is between 40 and 44 years old in the Over 40 category etc (I have around 20 categories so the formula is going to be pretty tall I guess.

Thanks

 

[MakeItSo]

I assume we are talking about Excel here?
Check the Excel Help for "Lookup table". That should neatly cover it.
;-)

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[SkipVought]

Hi,

Make a table using the LOWER age in the range, as the value. I used Age and Cat as the two columns, like...
[tt]
Age Cat
18 Youth
30 Open
40 Over 40
50 Way over
[/tt]
Then the formula, having NAMED THE RANGES...
[tt]
=INDEX(CAT, MATCH(Z1,Age,1),1)
[/tt]
where Z1 has my participant age.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bezzinajohn]

Thanks SkipVought looks great. But since I am quite a novice in this kind of thing would I be asking too much if you put what you proposed in an excel sheet and attach it here in this forum ? Plezzzzzzzz

 

[SkipVought]

This is not rocket science.

1. Set up your table with the exact headings I show (Age & Cat)

2. Enter all your data

3. Select ALL the cells in the column.

4. Insert > Names > Create - Create names in TOP row.

If you ADD or DELETE rows from this table, redo steps 3 & 4.

Now enter the formula on the first row containing the ages of your participants in a convenient column. Change the Z1 reference to the cell containing the age of the participant.. Then COPY the cell down thru all the rows of data for participants. Make sure to calculate (F9).

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bezzinajohn]

Hi SkipVought

Thanks for the explanation. Good news and bad. I managed on a version of Excel 2003 but this feature does not work in Excel 2007. And I simply cannot use the 2003 version.

How can it be done in 2007?

Thanks,

John

 

[SkipVought]

I managed on a version of Excel 2003

Copy the TABLE from 2003 and paste into 2007.

There's a way to create names in 2007, but I do not have a copy nor am I conversant enough to walk you thru. I believe that it is in the DATA "tab" list & tables or something like that.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

What was implied, but never stated in my latest reply was, 1) create then list and named ranges in 2003, using Insert > Name > Create -- Create names in TOP row.

THEN 2) copy the table into 2007.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

FYI: To create named ranges based on top row in Excel 2007, select all data ([Ctrl]+[A]), then go to Formulas > Defined Names > Create from Selection > Top Row.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[bezzinajohn]

Hi SkipVought

Thanks for your suggestion. To cut a long story short I managed but I did encounter a few odd problems like not being able to cut and paste the table but have to save it from one file to the other. I also had an issue with the positioning of the table.

Anyway - THANKS much appriciated!!

 

[bezzinajohn]

OK Great. Thanks!

 

[SkipVought]

I also had an issue with the positioning of the table.

The Table could be ANYWHERE in the workbook. The postion is irrelevent to the function, but might be relevent to other factors like user access (easy, hard or impossible to find)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]