I need a formula to rank my values so that duplicate values receive the same rank and subsequent values are ranked sequentially without skipping rank.
RANK.EQ skips rank value, and the temporary fix that I have used (below) is not sortable.Formula:
Please Login or Register to view this content.
Could this be solved with some use of FREQUENCY?
Please see attached sheet.
Try this ...
=SUMPRODUCT(($A$2:$A$45<=A2)/COUNTIF($A$2:$A$45,$A$2:$A$45))
Try this:
=SUMPRODUCT((A2 > A$2:A$45)/COUNTIF(A$2:A$45,A$2:A$45))+1
Glenn
Great! That does what I need. Thank you both.
Last edited by theTaoJones; 05-31-2017 at 11:28 AM.
One more question:
I'm going to set this up in a template to sort material lists. Some of the lists have thousands of entries, whereas others only have a hundred or so. Is there a way to adjust the endpoint of the array so that it automatically detects where the list ends instead of me having to manually manipulate it?
For example:
For the first list I am sorting to A45, but the second list only goes to A20. If I import the 19 item list into the table I get a #VALUE error unless I adjust the formula to read
Formula:Please Login or Register to view this content.
Is there a way to set it up for 4,000 entries ($A$4001) but have it autodetect if the list does not extend that far?
Phuocam,Originally Posted by Phuocam
I also had to make one more adjustment to the formula so that VLOOKUP could accurately read the numbers. Many of the entries round to #.9999... within the SUMPRODUCT calculation, so I added ROUNDUP.
Formula:Please Login or Register to view this content.
In the sheet you posted, there was a formula in A47. I assume that it is not present in your real data. here is a way, using a Named Range - CTRL-F3 to view - to make the column range fully dynamic. It's called data, and the formula becomes:
=SUMPRODUCT((A2 >data)/COUNTIF(data,data))+1
There is something strange about your last comment, concerning ROUNDUP. The formula returns INTEGERS. Can you explain where the VLOOKUP and problems arise? The problem lies elsewhere....
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
Glenn,
The named range solution works great. Thank you!
For the VLOOKUP issue, I've started a new thread here.
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
Thanks, this works for me in some cases. However, if I want to do this with an if condition, i.e. for different categories (like I before used to do with countifs statements) it doesn't work. Could anyone help me with this?
Capture.PNG
Cheers,
Jelco
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Forum Rules (updated September 2018): please read them here.
How to use the Power Query code you've been given: help here. More about the Power suite here.
Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.
Hi, could you help to explain how this formula works?
Thanks so much for your help!
Hello Glenn, could you help to explain the logic of this formula?
Thank you so much,
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks