formula for sequential rank with duplicate numbers???

[XLerific]

I am trying to get the rank of a number in a list excluding duplicates. See below for sample data and result desired. If I use the rank formula, the results go from 1-8. I would like the results to be sequential instead so there are no numbers skipped. let me know of any ways to get around this - thanks!


# Desired Results Rank Formula
113 1 1
364 2 2
407 3 3
407 3 3
427 4 5
752 5 6
805 6 7
861 7 8
861 7 8
861 7 8

 

[mathchick]

i currently just brought excel to a screeching halt, so I can't test this, but I've done it before, and here's how:

data in column A starting in cell 2
ranging in column B starting in cell 2

=max($B$2:B2)+if(B2=B1,1,0)

 

[XLerific]

unfort. I think this only works if the list is already in order. I put the data in order to better illustrate my problem but I'm trying to do this for a data list that will not already be in sequential order.

Thoughts?
Thanks!

 

[mathchick]

unfort. I think this only works if the list is already in order. I put the data in order to better illustrate my problem but I'm trying to do this for a data list that will not already be in sequential order.

Thoughts?
Thanks!

how about this?
=countif($A$1:$A$100,"<"&A1)