Ranking Without Duplicates and NOT Skipping Ranks

[Smok3y]

Hi there,

Apologies if this is straight forward but I can't seem to arrive at a solution here.

Trying to produce a rank for a column (column AA based on column Z in attached image) with unique ranks (no duplicates) and with no skipping rank numbers, currently using the below standard formula:

=RANK(Z2,$Z$2:$Z$784)+COUNTIF($Z$2:Z2,Z2)-1

*Please note I have only ranked by column Z for demonstrative purposes in the image, to show the issues below - normally these are ranked by another factor and the Total column is not sorted in ascending order.

Unsure why this is throwing up duplicates still, and what's more there seems to inconsistencies on where these duplicates land.

Cell Z22 and Z23 are equal in value, but they are ranked 21st and 23rd, and then Z24 also carries a rank of 23rd even though it is slightly less than the other two cells.

Further, Z29, Z30 AND Z31 are all equal in value but display no duplicate ranks (28th, 29th and 30th), but are then followed by 2 more equal values in Z32 and Z33 who show the duplicate rank of 32nd (skipped a rank number). The following rank is 33rd and doesn't skip so there seems to be inconsistent applications of these rank numbers.

To clarify, there are no further decimal points that are hidden that might separate these otherwise equal values shown.

Any help would be greatly appreciated.

Smok3y

 

[Zot]

You meant this?

PlayerRank.xlsx
	Y	Z	AA
1	PLAYER	TOTAL	RANK
2	TAYLOR WALKER	8.01	1
3	DUSTIN MARTIN	5.28	2
4	JACK MACRAE	4.23	3
5	CLAYTON OLIVER	4.2	4
6	CALLUM MILLS	3.61	5
7	TOM MITCHELL	3.41	6
8	MAX GAWN	3.22	7
9	CAMERON GUTHRIE	3.11	8
10	ANDREW BRAYSHAW	2.93	9
11	MARCUS BONTEMPELLI	2.93	10
12	TIM MEMBREY	2.79	11
13	BAILEY SMITH	2.76	12
14	JOSH BRUCE	2.75	13
15	TRAVIS BOAK	2.67	14
16	ZAK BUTTERS	2.66	15
17	SAM WICKS	2.65	16
18	BRODIE GRUNDY	2.59	17
19	JOEL SELWOOD	2.58	18
20	NOAH ANDERSON	2.57	19
21	TAYLOR ADAMS	2.45	20
22	DARCY PARISH	2.43	21
23	ERROL GULDEN	2.43	22
24	ANDREW GAFF	2.42	23
25	DAVID MUNDY	2.41	24
26	OLLIE WINES	2.39	25
27	HARRY MCKAY	2.37	26
28	TIM KELLY	2.26	27
29	PATRICK CRIPPS	2.25	28
30	SAM WALSH	2.25	29
31	DOM SHEED	2.25	30
32	RORY SLOANE	2.2	31
33	LUKE SHUEY	2.2	32
34	CHRISTIAN PETRACCA	2.17	33
35	STEVEN MAY	2.13	34
Sheet1

Cell Formulas
Range	Formula
AA2:AA35	AA2	=RANK(Z2,$Z$2:$Z$35,0)+COUNTIF($Z$2:Z2,Z2)-1

 

[Smok3y]

You meant this?

PlayerRank.xlsx
	Y	Z	AA
1	PLAYER	TOTAL	RANK
2	TAYLOR WALKER	8.01	1
3	DUSTIN MARTIN	5.28	2
4	JACK MACRAE	4.23	3
5	CLAYTON OLIVER	4.2	4
6	CALLUM MILLS	3.61	5
7	TOM MITCHELL	3.41	6
8	MAX GAWN	3.22	7
9	CAMERON GUTHRIE	3.11	8
10	ANDREW BRAYSHAW	2.93	9
11	MARCUS BONTEMPELLI	2.93	10
12	TIM MEMBREY	2.79	11
13	BAILEY SMITH	2.76	12
14	JOSH BRUCE	2.75	13
15	TRAVIS BOAK	2.67	14
16	ZAK BUTTERS	2.66	15
17	SAM WICKS	2.65	16
18	BRODIE GRUNDY	2.59	17
19	JOEL SELWOOD	2.58	18
20	NOAH ANDERSON	2.57	19
21	TAYLOR ADAMS	2.45	20
22	DARCY PARISH	2.43	21
23	ERROL GULDEN	2.43	22
24	ANDREW GAFF	2.42	23
25	DAVID MUNDY	2.41	24
26	OLLIE WINES	2.39	25
27	HARRY MCKAY	2.37	26
28	TIM KELLY	2.26	27
29	PATRICK CRIPPS	2.25	28
30	SAM WALSH	2.25	29
31	DOM SHEED	2.25	30
32	RORY SLOANE	2.2	31
33	LUKE SHUEY	2.2	32
34	CHRISTIAN PETRACCA	2.17	33
35	STEVEN MAY	2.13	34
Sheet1

Cell Formulas
Range	Formula
AA2:AA35	AA2	=RANK(Z2,$Z$2:$Z$35,0)+COUNTIF($Z$2:Z2,Z2)-1

Thanks Zot, that seems to have fixed it based on the image, can I ask what you actually changed with the formula here?

Only thing I can see is just the smaller array (extends to Z784, but just up to Z35 in the example), and the addition of the 0 as part of the 'Order' within the Rank function (which is the same as the default setting of not including isn't it)?

Appreciate the prompt response mate.

Cheers, Smok3y

 

[Zot]

Don't understand what you were saying. The zero is just for high value on top. If not putting zero also will be the same I believe. Zero is default value I think.

I see what you meant in your formula. I tried to captured data and forgot about your formula. I substitute with your formula, I did not produce what you were seeing. Something wrong with your Excel? I see no problem with mine. Your formula was just fine

In fact I just copy paste your formula and get same result

 

[Smok3y]

Don't understand what you were saying. The zero is just for high value on top. If not putting zero also will be the same I believe. Zero is default value I think.

I see what you meant in your formula. I tried to captured data and forgot about your formula. I substitute with your formula, I did not produce what you were seeing. Something wrong with your Excel? I see no problem with mine. Your formula was just fine

In fact I just copy paste your formula and get same result

Yeah I'm not sure what's going on tbh - relatively fluent with Excel but the output on my end makes no sense to me. Like you said my formula is essentially the same, but throwing up all of these inconsistencies. These ranks are supposed to feed through to a leaderboard but I'm getting a bunch of errors because I'm getting duplicates and skipping rank numbers

 

[Zot]

Yeah I'm not sure what's going on tbh - relatively fluent with Excel but the output on my end makes no sense to me. Like you said my formula is essentially the same, but throwing up all of these inconsistencies. These ranks are supposed to feed through to a leaderboard but I'm getting a bunch of errors because I'm getting duplicates and skipping rank numbers

Reinstall Excel perhaps

 

[Smok3y]

Reinstall Excel perhaps

So I just stumbled across something.

The thread name of the below caught my attention, I understand very little of the totality of the discussion. but it made me try something.

Error in Rank function when ranking values calculated from sums of reciprocals

It talks about errors in the Rank function and extremely small decimal numbers, rounding etc.

I placed a ROUND function around the SUM function currently sitting there, to 4 decimal places., and low and behold my issue is fixed.

=ROUND(SUM(C2:Y2),4)

I still don't understand the issue, none of the numbers in any column goes beyond 2 decimal places in the first place.

Seems like an Excel glitch for lack of a better term, maybe someone smarter than me can unpick this anomaly.

Appreciate the help in any case.

Cheers, Smok3y

 

[Zot]

So, in your case the numbers in column Z are calculation results from other cells? Perhaps, that is why I saw different result than yours. The link you provided when I tried, the rank result was 2, 2, 1.

At least we know that the Excel engine use calculation from internal register in memory with all possible decimal point, I suppose.

 

[Smok3y]

So, in your case the numbers in column Z are calculation results from other cells? Perhaps, that is why I saw different result than yours. The link you provided when I tried, the rank result was 2, 2, 1.

At least we know that the Excel engine use calculation from internal register in memory with all possible decimal point, I suppose.

Yeah that's correct, column Z was the sum of the columns to the left of it, again none of those numbers is more than 2 decimals though - so it never crossed my mind that decimal points beyond that was an issue. Not sure the rank result 2,2,1 you are referring to, I still don't understand how this was a factor haha...

 

[Zot]

Yeah that's correct, column Z was the sum of the columns to the left of it, again none of those numbers is more than 2 decimals though - so it never crossed my mind that decimal points beyond that was an issue. Not sure the rank result 2,2,1 you are referring to, I still don't understand how this was a factor haha...

I was recreating the example in the link. It is stated that But instead, the row that has the "3" in the first position will be ranked as 3 instead of 1 but instead of 3, I got 2 for 1st and 2nd rank ?