assuming your data is in A1:A6, try the following

=RANK(A1,$A$1:$A$6,1)+COUNTIF(A1:$A$6,A1)-1

and copy down. Be sure to note the the relative A1 in the count if formula.

Special nod to Aladin with further credit to Celia. I couldn't find the original string where Aladin provided this, so he may be able to clean it up a little.

This formula the first occurance will have the higher number rank.

--Thanks. This is perfect.

But what if there are 3 number which are identical and not just 2?

It should be fine. Here are my results with modifying your last number to a duplicate (triplicate).

What results are you expecting (if not this)?

What I'm trying to do is if there is a duplicate, then leave it blank, and then go to the next higher number and return a result..Example

A1 B1

200......1

110......2

110......

105......3

104......4

104......

104......

101......5

I'm ranking by highest number first...Scott

There is a "bug" in Ian's rendition from memory concerning the COUNTIF part. The formula must be:

=RANK(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1

I took up your original sample that contained numbers in column B.

Three identical numbers ought not to be a problem.

Aladin

Uniqify then custom rank to meet the new specs

A1 B1

Given the above specs, a different approach may be needed.

I'll assume the following data

{200;110;105;110;104;101;104;104}

in A2:A9. This is an unsorted version of your example.

In B2 array-enter: =SUM(IF(A2<$A$2:$A$9,1),1) [ copy down up to the last row of data ]

You need to hit CONTROL+SHIFT+ENTER at the same time (not just ENTER) to enter an array formula.

In C2 enter: =IF(ISNUMBER(MATCH(ROW()-ROW($B$2)+1,$B$2:$B$9,0)),INDEX($A$2:$A$9,MATCH(ROW()-ROW($B$2)+1,$B$2:$B$9,0)),"") [ copy down up to the last row of data ]

In D2 enter: =IF(ISNUMBER(C2),MAX($D$1:D1)+1,"") [ copy down up to the last row of data ]

You'll see the following in A2:D9 including everything:

{200,1,200,1;110,2,110,2;105,4,"","";110,2,105,3;104,5,104,4;101,8,"","";104,5,"","";104,5,101,5}

Aladin

Just to avoid confusion, my question was answered on the first try (Aladin's "cleanup" changed the order of duplicates, but in my case, it did not matter). I think there was another "Scott" who was looking for more info here as well. Glad to see my original question, and the subsequent answers helped.

Re: Uniqify then custom rank to meet the new specs

I don't know how you did it or what it means, but it works. My list of numbers is already sorted in descending order and your array formula you wrote me works in either case. Thank you and your co-workers for all the help you've given me today...Thanks a bunch, Scott