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!!

 

[markmzz]

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%.

Sorry, but if you're 44 with a score of 10, you're the 70th percentile. (look at your table-post#3).

Anyway, with my last array formula (use Ctrl+Shift+Enter and not only Enter) with the table of the post#3:

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

The results are:

Age/Score/Percentil
21/11/40%
31/13/99%
41/12/99%
44/10/70%
33/13/99%

Markmzz

 

[NicM0411]

Sorry, but if you're 44 with a score of 10, you're the 70th percentile. (look at your table-post#3).

Anyway, with my last array formula (use Ctrl+Shift+Enter and not only Enter) with the table of the post#3:

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

The results are:

Age/Score/Percentil
21/11/40%
31/13/99%
41/12/99%
44/10/70%
33/13/99%

Markmzz

Thats why I need this formula to work!
Not sure what I'm doing wrong but I dont get the formula to work, even when I press CTRL+SHIFT+ENTER.

Thanks for the help with this

 

[markmzz]

Thats why I need this formula to work!
Not sure what I'm doing wrong but I dont get the formula to work, even when I press CTRL+SHIFT+ENTER.

Thanks for the help with this

Could you post your formula?

And you are using the table of the post #3?

Markmzz

 

[NicM0411]

Could you post your formula?

And you are using the table of the post #3?

Markmzz

Yeah, I'm using table in post #3.

My formula is {=INDEX(A2:A10,MATCH(J2,INDEX(B2:G10,,MATCH(J1,IFERROR(RIGHT(B1:G1,2)+1,1))),-1))}.

My table is in A1:G10
Score is J1
Age is J2

This formula returns "#N/A". If I reverse my score to J2 and age to J1, the formula returns ".5"

 

[markmzz]

Yeah, I'm using table in post #3.

My formula is {=INDEX(A2:A10,MATCH(J2,INDEX(B2:G10,,MATCH(J1,IFERROR(RIGHT(B1:G1,2)+1,1))),-1))}.

My table is in A1:G10
Score is J1
Age is J2

This formula returns "#N/A". If I reverse my score to J2 and age to J1, the formula returns ".5"

NicM0411,

Look at your PM Box.

What are the values in J2 and J1?

And .5 is 50%.

Markmzz