Ranking Information

Copper Contributor

Ranking assistance

 

I am relatively new to excel and want to develop a table where data can be easily captured and analysed for use. This is the data I have:

Student Name    Visual Score   Auditory Score   Tactile Score

Jane Doe              24                       20                        30

John Doe              45                       15                       20

Jojo Doe              21                         40                      15

 

I know how to rank the scores across to tell me which is the highest using Min/Max function.

What I would like to get is the number of students who had visual as their 1st rank, the number of students who has Auditory as their first rank and the number of students who had Tactile as their first rank.

 

Any assistance would be appreciated

 

Thanks

3 Replies

Hi

 

For Visual:

=SUM(--(SUBTOTAL(4,OFFSET(B$2:D$2,{0;1;2},,,))=N(OFFSET(B$2,{0;1;2},,,))))

For Auditory:

=SUM(--(SUBTOTAL(4,OFFSET(B$2:D$2,{0;1;2},,,))=N(OFFSET(C$2,{0;1;2},,,))))

For Tactile:

=SUM(--(SUBTOTAL(4,OFFSET(B$2:D$2,{0;1;2},,,))=N(OFFSET(C$2,{0;1;2},,,))))

 

Thanks a lot @Detlef Lewin. It worked. Another question, how do I get the Ranking to show the text rather than the value. Eg.

Student Name      Visual        Auditory     Tactile   1st Ranking

Jane Doe                 30              28             22              30

 

How do I get 30 to be say Visual instead of 30?

 

And where can I do a course or tutorial to help me get better at formulas and functions?

 

Thanks

Hi

 

=LOOKUP(2,1/(B2:D2=MAX(B2:D2)),B$1:D$1)