Ranking Issue

[mohamed ali abdelgawad]

Hi Dears

i use this formula to get unique rank

=IFERROR((COUNTIF(F5:F$426,F5)-1)+RANK(F5,F$6:F$426),"")

But some times i didn't get some number
like you see here . no 10 rank

so ho to avoid this issue
example sheet

Thank

 

[mohamed ali abdelgawad]

Post a small sample of how you want your output look like.

Ok
If i use rank only this will be result , as you see below rank 17 , 10 , 2, 15 and 4 all of this ranks duplicate

	%	Rank
Sales 24	100%	26
Sales 25	84%	46
Sales 26	105%	17
Sales 27	115%	10
Sales 28	130%	2
Sales 29	97%	31
Sales 30	105%	17
Sales 90	90%	43
Sales 91	93%	37
Sales 92	96%	32
Sales 93	101%	24
Sales 94	110%	14
Sales 95	59%	49
Sales 96	105%	17
Sales 118	115%	10
Sales 119	90%	42
Sales 120	96%	34
Sales 121	93%	39
Sales 122	93%	38
Sales 123	139%	1
Sales 184	100%	28
Sales 185	97%	30
Sales 186	89%	44
Sales 187	40%	50
Sales 188	105%	17
Sales 242	119%	8
Sales 243	79%	48
Sales 244	101%	25
Sales 245	98%	29
Sales 246	109%	15
Sales 247	91%	41
Sales 248	96%	35
Sales 249	81%	47
Sales 250	96%	33
Sales 251	114%	13
Sales 320	104%	23
Sales 321	105%	17
Sales 322	120%	7
Sales 323	125%	4
Sales 324	105%	17
Sales 325	96%	36
Sales 326	124%	6
Sales 327	85%	45
Sales 328	115%	10
Sales 329	109%	15
Sales 330	125%	4
Sales 331	91%	40
Sales 338	100%	27
Sales 340	115%	9
Sales 341	130%	2

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

if use rank + count if
it gives me unique rank as you see below
But there's some numbers missing like #9

	%	Rank
Sales 24	100%	26
Sales 25	84%	46
Sales 26	105%	17
Sales 27	115%	10
Sales 28	130%	2
Sales 29	97%	31
Sales 30	105%	18
Sales 90	90%	43
Sales 91	93%	37
Sales 92	96%	32
Sales 93	101%	24
Sales 94	110%	14
Sales 95	59%	49
Sales 96	105%	19
Sales 118	115%	11
Sales 119	90%	42
Sales 120	96%	34
Sales 121	93%	39
Sales 122	93%	38
Sales 123	139%	1
Sales 184	100%	28
Sales 185	97%	30
Sales 186	89%	44
Sales 187	40%	50
Sales 188	105%	20
Sales 242	119%	8
Sales 243	79%	48
Sales 244	101%	25
Sales 245	98%	29
Sales 246	109%	15
Sales 247	91%	41
Sales 248	96%	35
Sales 249	81%	47
Sales 250	96%	33
Sales 251	114%	13
Sales 320	104%	23
Sales 321	105%	21
Sales 322	120%	7
Sales 323	125%	4
Sales 324	105%	22
Sales 325	96%	36
Sales 326	124%	6
Sales 327	85%	45
Sales 328	115%	12
Sales 329	109%	16
Sales 330	125%	5
Sales 331	91%	40
Sales 338	100%	27
Sales 340	115%	12
Sales 341	130%	3

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

you can download example sheet ant try it

 

[kelly mort]

So let us say you are ranking col. B then use
=iferror(rank(b1,$b$1:$b$10000)+countifs(b$2:b2,b2)-1,"")

 

[mohamed ali abdelgawad]

So let us say you are ranking col. B then use
=iferror(rank(b1,$b$1:$b$10000)+countifs(b$2:b2,b2)-1,"")

that's exactly what i do but i have missing numbers 9 or 10 or any other number
how to avoid that

 

[kelly mort]

I have your sheet here and i cant really get your intentions properly.

 

[MrExcel]

I ran Mohamed's sheet past the Excel MVP group and it is a very intriguing situation.

It is generally well known that Excel only had 15 digits of precision. That is why you find so many people complaining that they can not store their 16-digit credit card numbers in Excel without losing the last digit.

Some of my friends cracked open the XML of Mohamed's worksheet and discovered that Excel is actually storing seventeen digits . It turns out that Mohamed has four cells that appear in Excel as 115%.
But in the XML, those four cells are actually:
0.11499999999999999
0.11499999999999999
0.11499999999999999
0.11500000000000000

The maddening thing here is that Excel won't show more than 15 digits, so we can never see in the Excel user interface nor in VBA that the cell contains anything other than 115%.

COUNTIFS and =A2=A3 all treat all four of those cells as if they contain 115%.

But we can tell from how the RANK function and the Sort feature work that Excel is using the 16th and 17th digits. This causes problems.

Mohamed - your workaround is that when you calculate the percentage, you need to wrap that calculation in a rounding function to keep only 14 or 15 digits. =ROUND(A2/B2,15) will prevent this from happening again.

I am guessing this won't make you happy. But the fact is that it will solve your problem. The other solution would be to push Microsoft to make RANK and sorting less accurate, but that will slow Excel calculation down for 749,999,999 other people, and I am not going to advocate for less accuracy and slower Excel.

 

[mohamed ali abdelgawad]

I ran Mohamed's sheet past the Excel MVP group and it is a very intriguing situation.

It is generally well known that Excel only had 15 digits of precision. That is why you find so many people complaining that they can not store their 16-digit credit card numbers in Excel without losing the last digit.

Some of my friends cracked open the XML of Mohamed's worksheet and discovered that Excel is actually storing seventeen digits . It turns out that Mohamed has four cells that appear in Excel as 115%.
But in the XML, those four cells are actually:
0.11499999999999999
0.11499999999999999
0.11499999999999999
0.11500000000000000

The maddening thing here is that Excel won't show more than 15 digits, so we can never see in the Excel user interface nor in VBA that the cell contains anything other than 115%.

COUNTIFS and =A2=A3 all treat all four of those cells as if they contain 115%.

But we can tell from how the RANK function and the Sort feature work that Excel is using the 16th and 17th digits. This causes problems.

Mohamed - your workaround is that when you calculate the percentage, you need to wrap that calculation in a rounding function to keep only 14 or 15 digits. =ROUND(A2/B2,15) will prevent this from happening again.

I am guessing this won't make you happy. But the fact is that it will solve your problem. The other solution would be to push Microsoft to make RANK and sorting less accurate, but that will slow Excel calculation down for 749,999,999 other people, and I am not going to advocate for less accuracy and slower Excel.

Thanks MR Excel For your caring on my case
I'll use round to get rank as a facade

thanks for everyone reply to me

 

[MrExcel]

The 15/17 digit issue became the subject of my video # 2110 today:
https://youtu.be/WZfjmbEDbfI