I'm trying to sort the values on the A2:D6 according to their numbers.
I removed the letters on the range B2:B6.
Then I tried to put a RANK formula onto the range C2:C6 to rank them. It doesn't work for some reason?
I want to sort them on the rance D2:D6.
How can I do that?
The RIGHT function in column B will return a text value, not a number (you can see that these are aligned differently in the column). To overcome this you can change your formula in B2 to this:
=IF(LEN(A2)=2,--RIGHT(A2, 1), A2)
The double minus sign in front of the RIGHT function will ensure that a number is returned, so if you copy this down then your RANK formula will work correctly in column C.
Hope this helps.
Pete
Please try at D2
=INDEX(A$2:A$6,MOD(AGGREGATE(15,6,RIGHT(A$2:A$6)*10^6+ROW(A$2:A$6),ROWS(D$2:D2)),10^6)-ROW(A$1))
Why my RANK formula doesn't work?
(If It's because the formatting of the column B I formatted the column B as number and It doesn't work either.)
I had considered using RANK formula then applying INDEX & MATCH to it.
Last edited by zanshin777; 08-17-2020 at 02:08 PM.
I explained why it doesn't work (some of the values are text values), and suggested a way to get it to work (using the double minus).
Note that formatting will not change the underlying value, and the RANK formula only works with proper numbers.
Pete
Thank you very much Pete_UK and Bo_Ry.
Both methods work.
Then to be able to sort them I typed this formula on E2.
Formula:
Please Login or Register to view this content.
It works.
Last edited by zanshin777; 08-17-2020 at 10:38 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks