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

 

[Roxxien]

Hum, you had 2 times the rank 12 (Sales 328 and sales 340). I evaluated both formula and observed a difference in the rank part of the formula. One showed 10 and the other showed 9. Both cells equal 115%. I rewrote the second one and instantly, the problem was solved ...

I clearly don't understand the problematic here and will try to find the origin of this problem. If someone else know the reason in the forum, I hope they will help you.

 

[mohamed ali abdelgawad]

Hum, you had 2 times the rank 12 (Sales 328 and sales 340). I evaluated both formula and observed a difference in the rank part of the formula. One showed 10 and the other showed 9. Both cells equal 115%. I rewrote the second one and instantly, the problem was solved ...

I clearly don't understand the problematic here and will try to find the origin of this problem. If someone else know the reason in the forum, I hope they will help you.

thanks for your support
also i hope anyone solve it

 

[MrExcel]

There is something evil with your cell F344.
It is showing 115%. But it contains something slightly larger than 115%.
I can't figure out what is in there, but if I sort the percentages descending, Store 340 comes before Store 27, 118, and 328 which also contain 115%.

I figure it was a floating point issue, but there is no way that I can coax 115.00000001 out of that cell.

So, I can't tell you what is causing it. But I can tell you a simple fix: Go to cell F344. Type 115%. The formulas that you have will start working again.

 

[mohamed ali abdelgawad]

There is something evil with your cell F344.
It is showing 115%. But it contains something slightly larger than 115%.
I can't figure out what is in there, but if I sort the percentages descending, Store 340 comes before Store 27, 118, and 328 which also contain 115%.

I figure it was a floating point issue, but there is no way that I can coax 115.00000001 out of that cell.

So, I can't tell you what is causing it. But I can tell you a simple fix: Go to cell F344. Type 115%. The formulas that you have will start working again.

thanks MR.excel
this part of automated report all numbers generated automatically
I can't type number myself in any cell.

 

[mohamed ali abdelgawad]

can anyone help me please

 

[kelly mort]

Okay then what if you round the number? Maybe you round all up or down.

Like =roundup(A1,0) or rounddown(A1,0)
Based on what you wanna achieve so that you can do away with all floating points.
Regards
Kelly

 

[mohamed ali abdelgawad]

Okay then what if you round the number? Maybe you round all up or down.

Like =roundup(A1,0) or rounddown(A1,0)
Based on what you wanna achieve so that you can do away with all floating points.
Regards
Kelly

Round help in this case but numbers will not be accurate

is that any other formula to rank uniquely

 

[kelly mort]

How uniquely?

 

[mohamed ali abdelgawad]

How uniquely?

if i use =rank(number,ref) it will duplicate ranks , you will see two or three numbers in same rank

 

[kelly mort]

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