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

N

nardlje80

New Member
Joined
May 12, 2015
Messages
8
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.

ABCD
STUDENTSGRADESROUNDEDRANK
A87.2587#DIV/0!
B80.3080#DIV/0!
C80.1080#DIV/0!
#DIV/0!#DIV/0!#DIV/0!
#DIV/0!#DIV/0!#DIV/0!
E80.4080#DIV/0!
F87.0087#DIV/0!
G89.7590#DIV/0!

<tbody>
</tbody>
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
What is in the the cells under grades & rounded which is returning #DIV/0!

If you clear them your formula will work

Code: 
[TABLE="width: 260"]
<!--StartFragment--> <colgroup><col width="65" span="4" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="width: 65"]STUDENTS[/TD]
  [TD="width: 65"]GRADES[/TD]
  [TD="width: 65"]ROUNDED[/TD]
  [TD="width: 65"]RANK[/TD]
 [/TR]
 [TR]
  [TD]A[/TD]
  [TD="align: right"]87.25[/TD]
  [TD="align: right"]87[/TD]
  [TD="align: right"]2.5[/TD]
 [/TR]
 [TR]
  [TD]B[/TD]
  [TD="align: right"]80.3[/TD]
  [TD="align: right"]80[/TD]
  [TD="align: right"]5[/TD]
 [/TR]
 [TR]
  [TD]C[/TD]
  [TD="align: right"]80.1[/TD]
  [TD="align: right"]80[/TD]
  [TD="align: right"]5[/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]E[/TD]
  [TD="align: right"]80.4[/TD]
  [TD="align: right"]80[/TD]
  [TD="align: right"]5[/TD]
 [/TR]
 [TR]
  [TD]F[/TD]
  [TD="align: right"]87[/TD]
  [TD="align: right"]87[/TD]
  [TD="align: right"]2.5[/TD]
 [/TR]
 [TR]
  [TD]G[/TD]
  [TD="align: right"]89.75[/TD]
  [TD="align: right"]90[/TD]
  [TD="align: right"]1[/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]
 
Upvote 0
Thanks for your quick reply, sir. this sheet is linked from another sheet. that is why it has an error because its reference cell has no value.
 
Upvote 0
Sir, I tried this based from your suggestion =IFERROR(RANK.AVG(C2,C$2:C$9)) but it says I entered too few argument for this function.
 
Upvote 0
You need to use iferror on the formula in B5:C6

Code: 
[TABLE="width: 260"]
<!--StartFragment--> <colgroup><col width="65" span="4" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="width: 65"]STUDENTS[/TD]
  [TD="width: 65"]GRADES[/TD]
  [TD="width: 65"]ROUNDED[/TD]
  [TD="width: 65"]RANK[/TD]
 [/TR]
 [TR]
  [TD]A[/TD]
  [TD="align: right"]87.25[/TD]
  [TD="align: right"]87[/TD]
  [TD="align: right"]2.5[/TD]
 [/TR]
 [TR]
  [TD]B[/TD]
  [TD="align: right"]80.3[/TD]
  [TD="align: right"]80[/TD]
  [TD="align: right"]5[/TD]
 [/TR]
 [TR]
  [TD]C[/TD]
  [TD="align: right"]80.1[/TD]
  [TD="align: right"]80[/TD]
  [TD="align: right"]5[/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD="colspan: 2"]=iferror(your formula,"")[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]E[/TD]
  [TD="align: right"]80.4[/TD]
  [TD="align: right"]80[/TD]
  [TD="align: right"]5[/TD]
 [/TR]
 [TR]
  [TD]F[/TD]
  [TD="align: right"]87[/TD]
  [TD="align: right"]87[/TD]
  [TD="align: right"]2.5[/TD]
 [/TR]
 [TR]
  [TD]G[/TD]
  [TD="align: right"]89.75[/TD]
  [TD="align: right"]90[/TD]
  [TD="align: right"]1[/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]

And in your rank formula

=IFERROR(RANK.AVG(C2,C$2:C$9),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

msword
  • Solved
=AVERAGE: How to exclude cell with errors completely?
Replies
4
Views
165
etaf
etaf
M
  • Locked
  • Question
Problems with adapting a formula
Replies
1
Views
205
Fluff
Fluff
B
  • Question
Auto calculation of Percentage, Position and Grade
Replies
1
Views
329
awoohaw
awoohaw
A
  • Question
Help for a Newbie
Replies
6
Views
302
Alanesque
A
J
  • Solved
help finding my error
Replies
1
Views
131
Proulxs
P

Forum statistics

Threads
1,214,589
Messages
6,120,416
Members
448,960
Latest member
AKSMITH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top