Hi all,
I have to rank a table of figures which some happen to be duplicates. I used RANK function and it would rank the duplicates as the same.
I wish to have it rank by alphabetical value when the figures happen to be duplicates, but this is seriously splitting my skull... Hope the experts here can assist in this.
I have attached a sample. Thanks alot guys!
Lim
Last edited by JBeaucaire; 10-12-2012 at 01:23 PM.
hi Lim, try:
=IF(OR(E4="",E4=0),"",RANK(F4,$F$4:$F$27)+COUNTIF($F$4:F4,F4)-1)
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Hi benishiryo,
It works perfectly! thanks a million!
I have a question. I understand your formula for this,=IF(OR(E4="",E4=0),"",RANK(F4,$F$4:$F$27)
but i really cannot understand this part, COUNTIF($F$4:F4,F4)-1
Please would you explain it to me how this part really works? Sorry to bother you again.
Lim
It's counting how many OTHER times this particular rank has happened ABOVE this row. It will add what that count is to this rank, minus one, and the result is the "skipped" ranks get filled in by this additional math.
Click the cell and use the Evaluate Formula function to watch the formula unfold, should make it clearer.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
thank you so very very much...
I have marked this thread solved for you.
In the future please select Thread Tools from the menu above and mark the thread as solved. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks