I searched the forum and google for this, but haven't found anything. Seems like it should be relatively simple.
How do I rank filtered results? I do not wish to use VBA if possible.
I searched the forum and google for this, but haven't found anything. Seems like it should be relatively simple.
How do I rank filtered results? I do not wish to use VBA if possible.
Last edited by RobertGrumbles; 10-05-2009 at 08:53 AM.
If we assume columns of data are such that values resident in A2:D10 with D containing the numerics and E to contain the RANK then without using helper:
However this is a Volatile SUMPRODUCT and will be repeated on each row which is far from ideal (read: file could grind to a halt pending volume of data)Please Login or Register to view this content.
Another alternative might be to hold a helper calculation
With RANK now in F such that:Please Login or Register to view this content.
If in doubt please post a sample file with dummy data and expected results.Please Login or Register to view this content.
Last edited by DonkeyOte; 09-18-2009 at 11:18 AM. Reason: corrected references in F2 formula from F to E
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
If you have numbers in A2:A20 then try this formula in row 2 copied down
=IF(SUBTOTAL(3,A2),SUMPRODUCT(SUBTOTAL(3,OFFSET(A$2,ROW(A$2:A$20)-ROW(A$2),0)),(A$2:A$20>A2)+0)+1,"")
Last edited by daddylonglegs; 09-18-2009 at 10:44 AM.
Just to note that shg has since pointed out to me that I mistyped the last formula in my prior post such that it was referencing F rather than E - I've since corrected - apologies for confusion caused.
Donkeyote's post worked! Thanks!!
This formula by DonkeyOte seems to work, but is returning the numbers in the opposite order desired.
I.e. in a set of 13 filtered results, I want the highest value to return the highest number, and the lowest the lowest, whereas this does the opposite.
Any help on how to change this?
Just change the > to <
Audere est facere
Thanks a lot.
You've helped me create an amazing tool.
Last edited by iodine85; 04-30-2015 at 02:34 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks