0

I have some data which are grouped like this:

1st group of data(gender): Male, Female 2nd group of data(age): 12,13,14,15 3rd group (score + grade): For each gender and their corresponding age, there is a range of the scores grade A,b,c,d

For example: For a 12yr old male, 40 points will correspond a C grade. While a 14yr old female, 50 points will correspond a B grade.

how can i use excel to help me such that when I enter the age, gender and score, the correspond grade will come out?

THank you so much.

0

You could use a following formula to do this:

=INDEX($A$2:$A$5;MATCH(C8;INDEX($B$2:$G$5;0;MATCH(A8&B8;$B$1:$G$1;0));1))

the sheet looks like this (the above formula is in D8):

    A   B   C   D   E   F   G
1       13F 14F 15F 13M 14M 15M
2   D   0   0   0   0   0   0
3   C   11  12  13  12  13  14
4   B   21  22  23  22  23  24
5   A   31  32  33  32  33  34
6
7   Age Gen Sco Gra
8   15  M   22  C
9   14  F   22  B
10  14  M   22  C

The table with grades is in $A$1:$G$5, first row has concatenated age with gender, first column contains grade, and inside the table there is minimum score for the grade, gender and age.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.