Thanks:  0
Likes:  0

1. ## Ranking Issue

Hi Dears

i use this formula to get unique rank

Code:
=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

2. ## Re: Ranking Issue

Hello,

I see in your formula that you have 2 part in a sum. It's possible that this summation never equal some number.

I suggest you to use 2 formula separately (like in column H and I) with a sum of those two (like in J) to observe if this is your problem.

If this wasn't the origin of your problematic, can you explain why you use COUNTIF?

3. ## Re: Ranking Issue

Originally Posted by Roxxien
Hello,

I see in your formula that you have 2 part in a sum. It's possible that this summation never equal some number.

I suggest you to use 2 formula separately (like in column H and I) with a sum of those two (like in J) to observe if this is your problem.

If this wasn't the origin of your problematic, can you explain why you use COUNTIF?
I tried what you suggested but same result
and about why i use count if
i get this formula from this page which get ranks without duplicates

4. ## Re: Ranking Issue

Hum ok,

From what I'm seeing, to didn't use the same syntax for your COUNTIF. In your reference, they are locking the first cells of the range and you locked the last one.

Replace your formula by the following one. I don't know if it will solve your issue but it won't hurt to try.

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

5. ## Re: Ranking Issue

Originally Posted by Roxxien
Hum ok,

From what I'm seeing, to didn't use the same syntax for your COUNTIF. In your reference, they are locking the first cells of the range and you locked the last one.

Replace your formula by the following one. I don't know if it will solve your issue but it won't hurt to try.

Code:
=IFERROR((COUNTIF(F\$5:F5,F5)-1)+RANK(F5,F\$6:F\$426),"")
I tried It But now rank 9 is missing !!!

6. ## Re: Ranking Issue

Ok this already help to have the right information. However, now that I'm reading character by character your formula, I see something that is strange. Why are you searching the rank of the F5 cell in a range from F6 to F426? Is it possible that your F5 value is your missing rank?

7. ## Re: Ranking Issue

Originally Posted by Roxxien
Ok this already help to have the right information. However, now that I'm reading character by character your formula, I see something that is strange. Why are you searching the rank of the F5 cell in a range from F6 to F426? Is it possible that your F5 value is your missing rank?
Ok , now i corrected it to be the below but also Still missing number
you can try it yourself
Code:
=IFERROR(RANK(F5,\$F\$5:\$F\$425)+COUNTIF(\$F\$5:F5,F5)-1,"")

8. ## Re: Ranking Issue

Why did you changed your F426 to F425?

9. ## Re: Ranking Issue

Originally Posted by Roxxien
Why did you changed your F426 to F425?
because this is the range of data

10. ## Re: Ranking Issue

Originally Posted by Roxxien
Why did you changed your F426 to F425?
Please open example sheet and check it

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•