Forum Discussion

J Low's avatar
J Low
Copper Contributor
May 01, 2018

Ranking Information

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

Formulas and Functions

3 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    May 01, 2018

    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},,,))))

     

    • J Low's avatar
      J Low
      Copper Contributor
      May 01, 2018

      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