Rank by 3 position

[adirusf]

Hi, I'm trying to create 3 value level groups based on 3 control tests taken by my students. I managed to do one for each control test using "= countif" (rank test 1, 2 and 3). But I want to classify the students according to the best position they occupy in the 3 control tests. Can someone help me?

[adirusf]

Using function sum() to calculate value from ranking 1,2 and 3, I manage to use a formula to calculate the final rank

Please Login or Register  to view this content.

Please Login or Register  to view this content.

but this formula gives duplicate position like position 11 or 18.

[GeoffW283]

With column-K populated with the sum as you show in post #2, please try in L3 then copied down.

Please Login or Register  to view this content.

This will result in a unique rank for each student.

[GeoffW283]

Another couple of thoughts:

1) Would it be fairer to rank based on the sum of the test scores rather than the sum of ranks? There's much less chance of ties.

2) My post #3 formula ranks ties simply in the order in which they appear in the list. The following formula instead tiebreaks based on the sum of the test scores maybe a little fairer?

Please Login or Register  to view this content.

[MarvinP]

Hi adirusf and welcome to the forum,

I used to teach and used Student T Scores for all my tests. The idea is that an average score is 50 and one standard deviation above average was a 60. The reason for using these is that you can average them like in IQ or SAT scores. You need to find the average of the whole column and the standard deviation of that column and then calculate the Student T Score. It is a wonderful way to grade. See the attached where I've done it with your tests scores. Student T Score Ranks.xlsx

https://www.ftsd.org/site/handlers/f...t%20Scores.pdf

It really isn't a legal operation to average percentile scores or rank scores as there might be a very small difference between your scores, which might turn into large differences in ranks. I was the most accurate grader in my large district. I learned this method from my father-in-law who had a masters in education and taught at IBM and was on the world's Systems International committee. The USA should have gone metric many decades ago!!

[adirusf]

I appreciate your answers from both GeoffW283 and MarvinP. What I forgot to mention is that in test 1 a lower value means a better result while in test 2 and 3 it is the other way around, a higher value means a better result. I'm glad you mentioned the student test, I'll take it into account, but the ranking is not the one I want. Some of the students occupy a wrong position in the ranking, although they present better results in 2 of the 3 control tests. Thank you very much for your time, I hope to find another approach and make a ranking as accurate as possible based on the 3 tests.
The goal is to create 3 groups of 10 students in homogeneous groups.

[Bo_Ry]

Maybe try at K2

=SUMPRODUCT(--(MMULT($G$3:$I$32,{1;1;1})<=SUM(G3:I3)))

[adirusf]

Thanks Bo_Ry, for sure I'll save that formula.
A point-based formula... in test 1 the lowest values are closest to 10 points, where the min () value in the column represents a maximum of 10 points and in test 2 and 3 the max () value represents the best results, then I used average and finally with rank () I set the final rank, it's not perfect but if you have suggestions for improvement I'm waiting for them! Thank you.

[Bo_Ry]

Not an improvement, this is to get the same result without helper column.

=INDEX(SORT(CHOOSE({1,2},SEQUENCE(ROWS(B2:D31)),SORT(CHOOSE({1,2},MMULT(MOD(SMALL(B2:D31+SEQUENCE(,3)*10^6,ROWS(B2:D31)*{0,2,3}+{1,0,0}),10^6)^{1,-1,-1}*B2:D31^{-1,1,1},{1;1;1}),SEQUENCE(ROWS(B2:D31))),1,-1)),2),,1)

[adirusf]

Not an improvement, this is to get the same result without helper column.

=INDEX(SORT(CHOOSE({1,2},SEQUENCE(ROWS(B2:D31)),SORT(CHOOSE({1,2},MMULT(MOD(SMALL(B2:D31+SEQUENCE(,3)*10^6,ROWS(B2:D31)*{0,2,3}+{1,0,0}),10^6)^{1,-1,-1}*B2:D31^{-1,1,1},{1;1;1}),SEQUENCE(ROWS(B2:D31))),1,-1)),2),,1)

wow impressive formula, thanks a lot!