Creating a Ranking system for a Changing Range

I'm attempting to create a rank based on a range of data that will have a different number of rows daily. I know the range needs to start at Q5: but don't know what row the data will end at.

ie something like this:

=RANK(Q5,$Q$5:$Q$(match(9.9E+307,Q:Q,1)),1)

but the text in red is illegal.

Thanks, Jazz. I had to make a couple of small edits, but you got me moving the the right direction:

Formula:

Please Login or Register  to view this content.

Originally Posted by 6StringJazzer

It would be better to have your file but try this:

Formula:

Please Login or Register  to view this content.