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

[nardlje80]

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!

<tbody>
</tbody>

 

[gaz_chops]

I will be posting the content of my whole worksheet sir.

You don't need to post the whole sheet! Tell me what the formula is in cell B5?

 

[nardlje80]

Thanks for your assistance sir. The formula in B5 is ='1 (7)'!AR13 (it is linked in another worksheet). Anyway, I figured the formula =IF(COUNT(range),AVERAGE(range),"") for averaging that did not give a #DIV/0! error and =RANK.AVG(number,ref) for ranking that worked perfectly.

 

[gaz_chops]

Glad you're sorted, did you try this in B5

=iferror('1 (7)'!AR13,"")