Hi all,
I am having problems ranking a group of students by multiple criteria. The rank I am trying to do should be sorted by their overall result (column BK) and then by their name (column N) in case of ties.
The rank generally works well, but at low percentage results students with no overall result are ranked ahead of those with a low percentage results.
I doubt the actual value is significant but what I mean is the student with 35.72% overall is ranked 47, then ranked 48 and 49 are two students with no result in that column (so a blank cell), and then ranked 50th is a student with 34.67%. I would like the students with no result ranked lower than any student with a score.
The formula I am using to rank is
=IF(N4="zzz", 0, COUNTIF($BK$4:$BK$194, ">"&$BK4)+COUNTIFS($BK$4:$BK$194, $BK4, $N$4:$N$194, ">"&$N4)+1)
The "zzz" automatically gives a rank of 0 to the excess students in the class results pages.
Does anyone have any advice? I'm at a loss how to fix it.
I am having problems ranking a group of students by multiple criteria. The rank I am trying to do should be sorted by their overall result (column BK) and then by their name (column N) in case of ties.
The rank generally works well, but at low percentage results students with no overall result are ranked ahead of those with a low percentage results.
I doubt the actual value is significant but what I mean is the student with 35.72% overall is ranked 47, then ranked 48 and 49 are two students with no result in that column (so a blank cell), and then ranked 50th is a student with 34.67%. I would like the students with no result ranked lower than any student with a score.
The formula I am using to rank is
=IF(N4="zzz", 0, COUNTIF($BK$4:$BK$194, ">"&$BK4)+COUNTIFS($BK$4:$BK$194, $BK4, $N$4:$N$194, ">"&$N4)+1)
The "zzz" automatically gives a rank of 0 to the excess students in the class results pages.
Does anyone have any advice? I'm at a loss how to fix it.
