Ranking by group and breaking a tie

[monkeyspank]

I would like to Rank value by the month and breaking any ties ( anything where 2 or more might be ranked the same ). I would also like to exclude value that is blank from the ranking.

[TABLE="width: 202"]
<tbody>[TR]
[TD][TABLE="width: 255"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Measured Metric[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD="align: right"]11/1/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11/1/2015[/TD]
[TD="align: right"]1.384514629[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]11/1/2015[/TD]
[TD="align: right"]0.725428332[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]11/1/2015[/TD]
[TD="align: right"]0.628071767[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]11/1/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11/1/2015[/TD]
[TD="align: right"]1.368675384[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]11/1/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2015[/TD]
[TD="align: right"]0.868202299[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2015[/TD]
[TD="align: right"]0.555906058[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2015[/TD]
[TD="align: right"]0.598663348[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

What criteria should break the tie?

 

[mrshl9898]

Shouldn't the Rank for 12/1/2015 be 1,3,2 instead of 2,3,1?

If so, then try:

=IFERROR(RANK(B2,INDIRECT(ADDRESS(MATCH(A2,A$2:A2,0)+1,2)&":"&ADDRESS(COUNTIF(A:A,A2)+MATCH(A2,A$2:A2,0)+1-1,2))),"")

EDIT: Doesn't include tie breaker sorry.

 

[mrshl9898]

with tie breaker

=IFERROR(RANK(B2,INDIRECT(ADDRESS(MATCH(A2,A$2:A2,0)+1,2)&":"&ADDRESS(COUNTIF(A:A,A2)+MATCH(A2,A$2:A2,0)+1-1,2)))+COUNTIFS(A$2:A2,A2,B$2:B2,B2)-1,"")

 

[monkeyspank]

I'm not sure I am understanding how this formula works. I want to rank metric data in each month and if there are more than one that could be at a certain rank .. I would like a tiebreaker.

Month Metric Rank
[TABLE="width: 330"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]10/1/2016[/TD]
[TD="align: right"]1.359201107[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2016[/TD]
[TD="align: right"]0.828404297[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2016[/TD]
[TD="align: right"]0.764605503[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2016[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2016[/TD]
[TD="align: right"]1.949740035[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2016[/TD]
[TD="align: right"]0.51079656[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2016[/TD]
[TD="align: right"]1.032300388[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2016[/TD]
[TD="align: right"]1.184803944[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2016[/TD]
[TD="align: right"]1.311917819[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2016[/TD]
[TD="align: right"]0.917141466[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2016[/TD]
[TD="align: right"]0.984437812[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2016[/TD]
[TD="align: right"]0.584931827[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2016[/TD]
[TD="align: right"]0.625241491[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]