May 01 2018 06:27 AM
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
May 01 2018 07:08 AM
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},,,))))
May 01 2018 07:25 AM
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