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   Audito...
Formulas and Functions
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

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

    Hi

     

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