Rank Filtered Results

[RobertGrumbles]

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.

[DonkeyOte]

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:

Please Login or Register  to view this content.

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)

Another alternative might be to hold a helper calculation

Please Login or Register  to view this content.

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.

[daddylonglegs]

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,"")

[DonkeyOte]

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.

[RobertGrumbles]

Donkeyote's post worked! Thanks!!

[iodine85]

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?

[daddylonglegs]

Just change the > to <

[iodine85]

Thanks a lot.

You've helped me create an amazing tool.