I have data that has filters and hidden rows and my current formula ranks with ties which is what I want, but I do not want the ranking to skip numbers. Any help is much apprecaited.
Value Rank Now Rank I want
50 8 6
100 1 1
69 6 4
70 4 3
83 3 2
55 7 5
100 1 1
70 4 3
50 8 6
Here you go.
With your Value list in A2:A10
this regular formula, copied down, returns the rank-withou-gaps
EDITED TO INCLUDE THIS MORE ROBUST ALTERNATIVE
Please Login or Register to view this content.
Is that something you can work with?Please Login or Register to view this content.
Last edited by Ron Coderre; 05-19-2014 at 12:32 PM.
Will that show with hidden rows or filtered data? I think I need the subtotal function for it to work. Here is my current formula:
=SUMPRODUCT(SUBTOTAL(103,OFFSET($C$5:$C$300,ROW($C$5:$C$300)-ROW($C$5),0,1)),(C22<$C$5:$C$300)+0)+1
No...In my haste, I didn't notice the "filtered" requirement.
I'll put some thought into that. Meanwhile, somebody else may offer a solution.
Thanks Ron! I appreciate you looking at this!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks