Rank Function without Zeros

Dishboy09

New Member
Joined
Jun 11, 2017
Messages
34
Office Version
  1. 365
Platform
  1. Windows
I am needing help on a formula that ranks a set of cells. some will have data and some will not depending on the time the report is ran. I am attempting to exclude the data with zeros and no data from the rankings.

Current formula used : =IF(Q6=0,"",RANK(Q6,$Q$6:$Q$20,1))

PointsRank2
109
1210
1613
1210
1814
1412
2715
0
0
0
0
0
0
0
0

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Try

=IF(Q6=0,"",RANK(Q6,$Q$6:$Q$20,1)-COUNTIF(Q$6:Q$20,0))
 
Upvote 0
How about


Book1
AB
1PointsRank2
2101
3122
4165
5122
6186
7144
8277
90
100
110
120
130
140
150
160
Sheet 2
Cell Formulas
RangeFormula
B2=IF(A2=0,"",RANK(A2,IF($A$2:$A$16>0,$A$2:$A$16,0),1)-COUNTIF($A$2:$A$16,0))
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
Or use the countifs function ():

=IF(Q6=0,"",COUNTIFS($Q$6:$Q$20,"<>0",$Q$6:$Q$20,"<"&Q6)+1)
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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