Excel: HELP: How to rank.avg that excludes cells with error.


Good day! I am ranking my students final grade. But it gives me a #DIV/0! error. My formula is =RANK.AVG(C2,C$2:C$9). What I wanted is that A and F will rank 2.5 (because they occupy the 2nd and 3rd place) and B, C, and E will rank 5 (for places 4, 5 and 6). Another thing is that, I wish that the rounded grade will be the basis of ranking. Please help. I would like to say THANK YOU in advance for those who can help me. Below is a sample of my worksheet.

A B C D
STUDENTS GRADES ROUNDED RANK
A 87.25 87 #DIV/0!
B 80.30 80 #DIV/0!
C 80.10 80 #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
E 80.40 80 #DIV/0!
F 87.00 87 #DIV/0!
G 89.75 90 #DIV/0!


This question generated 11 answers. To proceed to the answers, click here.

This thread is current as of May 12, 2015.


For more resources for Microsoft Excel