If the list is in A1 to A7 I would have in B1:
=SUMPRODUCT(--($A$1:$A1=$A1))
and in B2
=SUMPRODUCT(--($A$1:$A$7<=$A1),--($B$1:$B$7=1))
Then copy to the seventh row.

"B. R.Ramachandran" wrote:

Bernie,
This is a follow-up of my previous response.
If there is a tie, (e.g., banana, apple, banana, grapes), the formula would
rank them 2,1,2,and 4 respectively. I am curious, how you would modify the
formula to make grapes rank 3 (and not 4)?
Regards,
B.R. Ramachandran

"Bernie Deitrick" wrote:

You could simply sort the column ascending.

If your values are in a1:a5, then the formula

=SUMPRODUCT((A1$A$1:$A$5)*1)+1

copied down for 5 rows will give the ranking.

--
HTH,
Bernie
MS Excel MVP


"cdavidson" wrote in message
...
I'll use a simple example...

I have 5 words I need to rank in ascending order, alphabetically.

Carrot
Peach
Apple
Salad
Banana


I would like the rank function to produce the following results:

Rank Name
------ -------
1 Apple
2 Banana
3 Carrot
4 Peach
5 Salad

How please? Many thanks.