Data Extract Help

[NicM0411]

I'm an admitted Excel novice. The back story is I'm an Occupational Therapist that the county approached about creating a program where I physical test police officers on several standardized fitness tests and more or less showing them that they aren't as "in shape" as they think they are. To do this, I'm considering their gender, age, and their performance on a fitness test and then comparing that score to a norm. Doing this by hand is time consuming and I want to provide this results immediately to them. So I created 2 sheets in excel. The first, I will give to the officers at the conclusion of all testing, this sheet will have there gender, age, their score on each test and their percentile that they fall in to. The 2nd sheet is where I entered all my "norms" data into. Its 2 tables per test of varying columns and rows. The tables are divided for "Male" and "Female". Each table then has % in the first column (99%,90%,80%, etc.) and the first row has age brackets (20-29,30-39, etc.). In the cells within the table is the "scores". SO...the problem I have is taking the known data for each participant (Gender, Age and Score) and returning the percentile on the first sheet. I've tried several different combinations. I'm pretty sure I need "IF" function for gender to establish appropriate table but after that it goes down hill for me. I've tried index, lookup, match...I've watched hours of YouTube videos and I feel like I'm close but I'm pretty sure its over my head. Not sure if its a reverse lookup I need but I have duplicate values in each row and I can't come up with the formula to return the correct value. Any help would be appreciated. Thanks in advance!!

 

[Fazza]

welcome, Nic

it will be MUCH easier to help you if you post some sample data. both the inputs and expected output/s

regards

 

[NicM0411]

	<20	21-30	31-40	41-50	51-60	61-70
99%	20	15	13	12	11	10
80%	15	14	13	12	10	9
70%	14	13	11	10	9	8
50%	13	12	10	9	8	7
40%	11	11	9	8	7	6
30%	10	10	8	7	6	5
20%	9	9	7	6	5	4
10%	8	8	6	5	4	3
5%	7	6	5	4	3	2

<tbody>
</tbody>

 

[NicM0411]

Above is a quick example of the data table with age across the top, the scores within the body of the table and percentile on the left. I would like to have the % returned to me after giving excel the age and the score. The problem that I have is that there are duplicate numbers in columns and I can't figure out function to return appropriate answer.

 

[NicM0411]

Sorry for so many posts but last one. An example would be I have a 21 year old who scored 11 on fitness test. I would excel to return to me, 40%.

 

[Fazza]

If that table is posted from range "A1" and the age is in "A12" and score in "B12", something like below? hth

=INDEX($A$2:$A$10,MATCH(B12,OFFSET($A$2:$A$10,,MATCH(A12,{0,21,31,41,51,61})),-1))

where I've hard coded the {0,21,31,41,51,61} to simplify looking up the age in the text entries "B1:G1"

 

[markmzz]

Maybe this array formula (use Ctrl+Shift+Enter and not only Enter):

J1-Score
J2-Age

J3-> =INDEX(A2:A10,MATCH(J2,INDEX(B2:G10,,MATCH(J1,IFERROR(RIGHT(A1:F1,2)+1,0))),0))

Markmzz

 

[markmzz]

Maybe this array formula (use Ctrl+Shift+Enter and not only Enter):

J1-Score
J2-Age

J3-> =INDEX(A2:A10,MATCH(J2,INDEX(B2:G10,,MATCH(J1,IFERROR(RIGHT(A1:F1,2)+1,0))),0))

Markmzz

A small modification:

J3-> =INDEX(A2:A10,MATCH(J2,INDEX(B2:G10,,MATCH(J1,IFERROR(RIGHT(A1:F1,2)+1,[COLOR="#FF0000"][B]1[/B][/COLOR]))),[COLOR="#FF0000"][B]-1[/B][/COLOR]))

Markmzz

 

[markmzz]

Above is a quick example of the data table with age across the top, the scores within the body of the table and percentile on the left. I would like to have the % returned to me after giving excel the age and the score. The problem that I have is that there are duplicate numbers in columns and I can't figure out function to return appropriate answer.

What are the expected results for age 31-40 and score 13 and age 41-50 and score 12?

Markmzz

 

[NicM0411]

I would expect results to be 99% for both. I want to give excel the "age" and "score" and return to me the percentile based on those 2 lookups. So, if you're 44 with a score of 10, you're the 50th percentile. Make sense? I think where I'm screwing up the function is that is your 33 and score 13 you're 99%.

What are the expected results for age 31-40 and score 13 and age 41-50 and score 12?

Markmzz