Rank Function without Zeros

[Dishboy09]

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))

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]Points[/TD]
[TD="class: xl64, width: 64"]Rank2[/TD]
[/TR]
[TR]
[TD="class: xl63"]10[/TD]
[TD="class: xl63"]9[/TD]
[/TR]
[TR]
[TD="class: xl63"]12[/TD]
[TD="class: xl63"]10[/TD]
[/TR]
[TR]
[TD="class: xl63"]16[/TD]
[TD="class: xl63"]13[/TD]
[/TR]
[TR]
[TD="class: xl63"]12[/TD]
[TD="class: xl63"]10[/TD]
[/TR]
[TR]
[TD="class: xl63"]18[/TD]
[TD="class: xl63"]14[/TD]
[/TR]
[TR]
[TD="class: xl63"]14[/TD]
[TD="class: xl63"]12[/TD]
[/TR]
[TR]
[TD="class: xl63"]27[/TD]
[TD="class: xl63"]15[/TD]
[/TR]
[TR]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Try

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

 

[Fluff]

How about

Book1
	A	B
1	Points	Rank2
2	10	1
3	12	2
4	16	5
5	12	2
6	18	6
7	14	4
8	27	7
9	0
10	0
11	0
12	0
13	0
14	0
15	0
16	0
Sheet 2

Cell Formulas
Range	Formula
B2	=IF(A2=0,"",RANK(A2,IF($A$2:$A$16>0,$A$2:$A$16,0),1)-COUNTIF($A$2:$A$16,0))

 

[Joe4]

See here: https://www.extendoffice.com/documents/excel/4326-excel-rank-ignore-zero.html

 

[Dishboy09]

Thanks everyone that got it!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Peter_SSs]

Thanks everyone that got it!

You're welcome.

 

[Phuoc]

Or use the countifs function ():

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