Ranking Issue

mohamed ali abdelgawad

Board Regular
Joined
Nov 18, 2016
Messages
71
Office Version
  1. 365
Platform
  1. Windows
Hi Dears

i use this formula to get unique rank

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

Screenshot_2017-07-10_19.13.16.png


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

Screenshot_2017-07-10_19.13.25.png



so ho to avoid this issue
example sheet

Thank
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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?
 
Upvote 0
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?

Thanks for your reply
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
 
Upvote 0
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),"")
 
Last edited:
Upvote 0
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 !!!
 
Upvote 0
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?
 
Upvote 0
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,"")
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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
Back
Top