Rank Function without Zeros

Dishboy09

New Member
Joined
Jun 11, 2017
Messages
32
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>
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Try

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,137
Office Version
  1. 365
Platform
  1. Windows
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Points</td><td style=";">Rank2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">12</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">16</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">12</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">18</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">14</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">27</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">0</td><td style=";"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet 2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=IF(<font color="Blue">A2=0,"",RANK(<font color="Red">A2,IF(<font color="Green">$A$2:$A$16>0,$A$2:$A$16,0</font>),1</font>)-COUNTIF(<font color="Red">$A$2:$A$16,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Dishboy09

New Member
Joined
Jun 11, 2017
Messages
32
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks everyone that got it!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,137
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Phuoc

Active Member
Joined
Apr 29, 2016
Messages
284
Or use the countifs function ():

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

Watch MrExcel Video

Forum statistics

Threads
1,122,939
Messages
5,598,967
Members
414,269
Latest member
FJXMTT

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
Top