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.

Assuming that A1:A5 contains your data, try...

B1, copied down:

=INDEX($A$1:$A$5,MATCH(SMALL(COUNTIF($A$1:$A$5,"<" &$A$1:$A$5),ROWS($B$1:B
1)),COUNTIF($A$1:$A$5,"<"&$A$1:$A$5),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

C1, copied down:

=COUNTIF($B$1:$B$5,"<"&B1)+1

Hope this helps!

In article ,
cdavidson wrote:

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.

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.

Bernie,
The formula you have suggested is extremely elegant (in the way it works).
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.

B. R.,

Thanks. I would like to take credit for it, but I learned that technique a looooong time ago from
people who are much smarter <vbg

Bernie
MS Excel MVP

Bernie,
The formula you have suggested is extremely elegant (in the way it works).
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

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.

I'm not aware of a technique using a single worksheetfunction that can do that - I would write a
User-Defined-Function, or use multiple columns of formulas.

HTH,
Bernie
MS Excel MVP


"B. R.Ramachandran" wrote in message
...
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.

Assuming that A1:A4 contains your data, try...

B1, copied down:

=SUM(IF(A1$A$1:$A$4,1/COUNTIF($A$1:$A$4,$A$1:$A$4)))+1

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"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,
That's what I thought.
Thanks for your prompt response.
B.R.Ramachandran

"Bernie Deitrick" wrote:

I'm not aware of a technique using a single worksheetfunction that can do that - I would write a
User-Defined-Function, or use multiple columns of formulas.

HTH,
Bernie
MS Excel MVP


"B. R.Ramachandran" wrote in message
...
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.

Hello,

a stable sort which would leave identical items in it's original order (I
assume the data in column A):

B1:

=COUNTIF($A:$A,"<"&A1)+COUNTIF($A$1:A1,A1)

C1:

=INDEX(A:A,MATCH(ROW(),B:B,FALSE))

Copy down...

Regards,
Bernd
--
Excel 2002

Excellent. The 1/COUNTIF idea is very clever.
B.R. Ramachandran

"Domenic" wrote:

Assuming that A1:A4 contains your data, try...

B1, copied down:

=SUM(IF(A1$A$1:$A$4,1/COUNTIF($A$1:$A$4,$A$1:$A$4)))+1

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"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

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.

Thank you Bernie, your formula worked perfectly for my simple example.
However, my apologies, I now realize I should have mentioned that my real
data set contains quite a few blank cells in the column of data I am trying
to rank (see revised example below). I need to exclude the blank cells from
the rankings, so can I ask you to offer an enhance formula to accomplish
this? Many thanks.


A1: Carrot
A2: Peach
A3:
A4: Apple
A5: Salad
A6:
A7: Banana


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

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







"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.

In B1, use the formula

=IF(A1="","",SUMPRODUCT((A1$A$1:$A$7)*($A$1:$A$7< ""))+1)

and copy down to match your list.

HTH,
Bernie
MS Excel MVP


"cdavidson" wrote in message
...
Thank you Bernie, your formula worked perfectly for my simple example.
However, my apologies, I now realize I should have mentioned that my real
data set contains quite a few blank cells in the column of data I am trying
to rank (see revised example below). I need to exclude the blank cells from
the rankings, so can I ask you to offer an enhance formula to accomplish
this? Many thanks.


A1: Carrot
A2: Peach
A3:
A4: Apple
A5: Salad
A6:
A7: Banana


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

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







"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.