Ranking help

[christinekenziee]

I am trying to rank students in such a way so that if they receive a zero it will not rank them it will skip over them but then if two students have the same score they will receive the same rank. I also would like it where it will not jump around with the ranks.
Example of how I would like it to look:

 

[Almach]

Hello Christine,

I did some testing and this is working on my end. You will just need to change the ranges to fit your data set.

=IF(A2=0,"",SUMPRODUCT((A2<=$A$2:$A$18)/COUNTIF($A$2:$A$18,$A$2:$A$18)))

 

[christinekenziee]

Hello Christine,

I did some testing and this is working on my end. You will just need to change the ranges to fit your data set.

=IF(A2=0,"",SUMPRODUCT((A2<=$A$2:$A$18)/COUNTIF($A$2:$A$18,$A$2:$A$18)))

Awesome that works perfectly, thank you!


Could you possibly answer this question now?
I am trying to find an average over of multiple ranks for the same student on different topics. The columns are not consecutive and I would like to skip blanks. So if that ranked 2nd, 4th, 6th and then did not rank on the 4th topic and got blank I would like the formula to skip that 4th topic. So it would only be averaging the first 3 topics.

 

[Almach]

You can just use the average function in excel and it'll do it for you. Since the formula you are using returns a blank cell instead of a number, excel wont include that in the average

Im just assuming your columns start at A in the picture above. This formula should work for you. I put an if error for those bottom two rows that have no rank at all to put in a N/A you can change that to whatever suits your workbook best.

=IFERROR(AVERAGE(C3,F3,H3,J3),"N/A")

If you dont want to have decimal places and show only whole ranks, you can either change the cell formats or just add the round function to the formula.

=IFERROR(ROUND(AVERAGE(C3,F3,H3,J3),0),"N/A")