Ranking Without Duplicates and NOT Skipping Ranks

Smok3y

New Member
Joined
Apr 9, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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
 

Attachments

  • PlayerRank.PNG
    PlayerRank.PNG
    33.8 KB · Views: 7

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows
You meant this?
PlayerRank.xlsx
YZAA
1PLAYERTOTALRANK
2TAYLOR WALKER8.011
3DUSTIN MARTIN5.282
4JACK MACRAE4.233
5CLAYTON OLIVER4.24
6CALLUM MILLS3.615
7TOM MITCHELL3.416
8MAX GAWN3.227
9CAMERON GUTHRIE3.118
10ANDREW BRAYSHAW2.939
11MARCUS BONTEMPELLI2.9310
12TIM MEMBREY2.7911
13BAILEY SMITH2.7612
14JOSH BRUCE2.7513
15TRAVIS BOAK2.6714
16ZAK BUTTERS2.6615
17SAM WICKS2.6516
18BRODIE GRUNDY2.5917
19JOEL SELWOOD2.5818
20NOAH ANDERSON2.5719
21TAYLOR ADAMS2.4520
22DARCY PARISH2.4321
23ERROL GULDEN2.4322
24ANDREW GAFF2.4223
25DAVID MUNDY2.4124
26OLLIE WINES2.3925
27HARRY MCKAY2.3726
28TIM KELLY2.2627
29PATRICK CRIPPS2.2528
30SAM WALSH2.2529
31DOM SHEED2.2530
32RORY SLOANE2.231
33LUKE SHUEY2.232
34CHRISTIAN PETRACCA2.1733
35STEVEN MAY2.1334
Sheet1
Cell Formulas
RangeFormula
AA2:AA35AA2=RANK(Z2,$Z$2:$Z$35,0)+COUNTIF($Z$2:Z2,Z2)-1
 

Smok3y

New Member
Joined
Apr 9, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
You meant this?
PlayerRank.xlsx
YZAA
1PLAYERTOTALRANK
2TAYLOR WALKER8.011
3DUSTIN MARTIN5.282
4JACK MACRAE4.233
5CLAYTON OLIVER4.24
6CALLUM MILLS3.615
7TOM MITCHELL3.416
8MAX GAWN3.227
9CAMERON GUTHRIE3.118
10ANDREW BRAYSHAW2.939
11MARCUS BONTEMPELLI2.9310
12TIM MEMBREY2.7911
13BAILEY SMITH2.7612
14JOSH BRUCE2.7513
15TRAVIS BOAK2.6714
16ZAK BUTTERS2.6615
17SAM WICKS2.6516
18BRODIE GRUNDY2.5917
19JOEL SELWOOD2.5818
20NOAH ANDERSON2.5719
21TAYLOR ADAMS2.4520
22DARCY PARISH2.4321
23ERROL GULDEN2.4322
24ANDREW GAFF2.4223
25DAVID MUNDY2.4124
26OLLIE WINES2.3925
27HARRY MCKAY2.3726
28TIM KELLY2.2627
29PATRICK CRIPPS2.2528
30SAM WALSH2.2529
31DOM SHEED2.2530
32RORY SLOANE2.231
33LUKE SHUEY2.232
34CHRISTIAN PETRACCA2.1733
35STEVEN MAY2.1334
Sheet1
Cell Formulas
RangeFormula
AA2:AA35AA2=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

Well-known Member
Joined
Nov 26, 2020
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows
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 :unsure:

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

Smok3y

New Member
Joined
Apr 9, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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 :unsure:

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 :mad:
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows
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 :mad:
Reinstall Excel perhaps ;)
 

Smok3y

New Member
Joined
Apr 9, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 
Solution

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows
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. (y)
 

Smok3y

New Member
Joined
Apr 9, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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. (y)
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows
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 🧐
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,226
Members
417,131
Latest member
Seanr19871

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