Ranking without Duplicates

[Gol4Q]

I have found within this website/forum the formula of RANK and COUNTIF... for some reason, it keeps returning problems. See below - any help greatly appreciated: (starts at E26)

[TABLE="width: 148"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]RANKINGS -
COL E F G[/TD]
[/TR]
[TR]
[TD]Team 1[/TD]
[TD="align: right"]14.00[/TD]
[TD="align: right"]6.00[/TD]
[/TR]
[TR]
[TD]Team 2[/TD]
[TD="align: right"]14.80[/TD]
[TD="align: right"]1.00[/TD]
[/TR]
[TR]
[TD]Team 3[/TD]
[TD="align: right"]14.20[/TD]
[TD="align: right"]4.00[/TD]
[/TR]
[TR]
[TD]Team 4[/TD]
[TD="align: right"]14.10[/TD]
[TD="align: right"]5.00[/TD]
[/TR]
[TR]
[TD]Team 5[/TD]
[TD="align: right"]14.70[/TD]
[TD="align: right"]3.00[/TD]
[/TR]
[TR]
[TD]Team 6[/TD]
[TD="align: right"]14.80[/TD]
[TD="align: right"]3.00[/TD]
[/TR]
</tbody>[/TABLE]

Formula for Column G for team 4 is: =RANK(F29,$F$26:$F$31,0)+COUNTIF($F$26:F29,F29)-1
Formula for Column G for team 5 is: =RANK(F30,$F$26:$F$31,0)+COUNTIF($F$26:F30,F30)-1
Formula for Column G for team 6 is: =RANK(F31,$F$26:$F$31,0)+COUNTIF($F$26:F31,F31)-1

Team 5 and 6 repeat their RANK in column G, and I can't figure out why... other renditions of the formula spit out a number outside of the range (if only 6 numbers, one will show a rank of "7")

Struggle is real - thanks for any help

 

[dave3009]

Do you start in G26 with the ranking? This is the result I get

Excel 2007
	E	F	G
26	Team 1	14	6
27	Team 2	14.8	1
28	Team 3	14.2	4
29	Team 4	14.1	5
30	Team 5	14.7	3
31	Team 6	14.8	2
Sheet4

Cell Formulas
Range	Formula
G26	=RANK(F26, $F$26:$F$31)+COUNTIF($F$26:F26, F26)-1
G27	=RANK(F27, $F$26:$F$31)+COUNTIF($F$26:F27, F27)-1
G28	=RANK(F28, $F$26:$F$31)+COUNTIF($F$26:F28, F28)-1
G29	=RANK(F29, $F$26:$F$31)+COUNTIF($F$26:F29, F29)-1
G30	=RANK(F30, $F$26:$F$31)+COUNTIF($F$26:F30, F30)-1
G31	=RANK(F31, $F$26:$F$31)+COUNTIF($F$26:F31, F31)-1

 

[Gol4Q]

My excel must be acting wrong - because with your formulas detailed above - mine shows team 5 and team 6 with a ranking of 3...

 

[dave3009]

Hi Gol4Q

Forgot to welcome you to the board in my first post - Welcome to MrExcel :D

That does seem odd, is there anything else acting on the sheet. Try highlighting the cells with the rank and checking the cell formats (Ctrl+F1). I can't imagine it'd be VBA as this would result in the rank being a straight up value. Check calculation isn't set to manual, press F9 and see if the result changes if it does then open up Excel options, go to formulas and change it from manual to automatic.

HTH

Dave

 

[Gol4Q]

still not working - having other issues. Wish I could attached a document for your review...

Ugh

 

[dave3009]

You can upload your file to one of the many file hosting sites and post a link to it.

 

[jtakw]

Hi,

Your F Column values, are they results of formula(s), if so, can you show the formula?, or may be wrap it with the ROUND function like: =ROUND(your formula,2)

 

[Gol4Q]

That did it - awesome! Thank you - if you have time, when you have time - so I can learn... why did that work? The "F" column was simply a formula --> =J7 --> and so on, taking on a sum function from other cell.

Thanks

 

[jtakw]

That did it - awesome! Thank you - if you have time, when you have time - so I can learn... why did that work? The "F" column was simply a formula --> =J7 --> and so on, taking on a sum function from other cell.

Thanks

You're welcome, welcome to the forum.

Without "seeing" your data...if your F column values are taken from another cell (e.g. corresponding J column cells) which are the results of a formula(s)...if you check those cells' value by cell formatting and expanding the decimal places beyond 2 to whatever (e.g. 10), you'll probably see that 14.7 may actually be 14.7443388984 or your 14.8 may actually be 14.7444522118, and thou I'm not certain of the EXACT number of digits Excel uses to calculate, I believe it's up to 15 decimal places, which as you can see may throw off your results. By wrapping the formula with the ROUND function to limit the value to 2 decimal places, Excel will calculate ONLY up to 2 decimal places, bypassing the possible error mentioned above.

More on the ROUND function: https://support.office.com/en-us/article/ROUND-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c