Hey everyone...

I was wondering what the easiest way would be to compare 3 nmueric
values, rank them, and return 3 "text" results. Here is my issue:

I have 3 cells, A1, A2 and A3, that look at numerical values in cells
B1, B2 and B3. What I need to do is have the rankings, 1st, 2nd and
3rd appear in the cells in the A column (respectively), based on the
"position" of the numeric values in the B column. So in short...

A B
1st 10
2nd 9
3rd 8

or...

A B
2nd 9
1st 10
3rd 8

The numbers in column B will change constantly, so I need column A to
adjust automatically.

Thanks to everyone for any help on this!!

Use CHOOSE()


In A1, A2, A3 put:
=CHOOSE((B1B2)+(B1B3)+1,"3rd","2nd","1st")
=CHOOSE((B2B3)+(B2B1)+1,"3rd","2nd","1st")
=CHOOSE((B3B1)+(B3B2)+1,"3rd","2nd","1st")

--
Gary''s Student


"stacy" wrote:

Hey everyone...

I was wondering what the easiest way would be to compare 3 nmueric
values, rank them, and return 3 "text" results. Here is my issue:

I have 3 cells, A1, A2 and A3, that look at numerical values in cells
B1, B2 and B3. What I need to do is have the rankings, 1st, 2nd and
3rd appear in the cells in the A column (respectively), based on the
"position" of the numeric values in the B column. So in short...

A B
1st 10
2nd 9
3rd 8

or...

A B
2nd 9
1st 10
3rd 8

The numbers in column B will change constantly, so I need column A to
adjust automatically.

Thanks to everyone for any help on this!!

On 8 Sep 2005 16:40:07 -0700, "stacy" wrote:

Hey everyone...

I was wondering what the easiest way would be to compare 3 nmueric
values, rank them, and return 3 "text" results. Here is my issue:

I have 3 cells, A1, A2 and A3, that look at numerical values in cells
B1, B2 and B3. What I need to do is have the rankings, 1st, 2nd and
3rd appear in the cells in the A column (respectively), based on the
"position" of the numeric values in the B column. So in short...

A B
1st 10
2nd 9
3rd 8

or...

A B
2nd 9
1st 10
3rd 8

The numbers in column B will change constantly, so I need column A to
adjust automatically.

Thanks to everyone for any help on this!!

With just three, it is relatively simple:

=CHOOSE(RANK(B1,$B$1:$B$3),"1st","2nd","3rd")

But it gets a bit more complicated with more rankings. So a more general
formula might be:

=RANK(B1,B:B)&IF(AND(MOD(RANK(B1,B:B),100)=11,
MOD(RANK(B1,B:B),100)<=19),"th",IF(MOD(RANK(
B1,B:B),10)=1,"st",IF(MOD(RANK(B1,B:B),10)=2,"nd",
IF(MOD(RANK(B1,B:B),10)=3,"rd","th"))))


--ron

Thank you... That works perfectly. However, I think I have added a
small wrinkle. I can now rank them just fine, but is there a way to
Rank, Compare and label them as well? Let me try to outlin below...

A 100 First
B 50 Second
C 100 First

What I would like to do is write something that ranks them, but then
looks at the values, and if two or more have the same value, simply say
"Tied". So it would look like so in the next example...

A 50 Tied
B 50 Tied
C 100 First

Thanks ahead of time for any advice...

On 10 Sep 2005 09:24:15 -0700, "stacy" wrote:

Thank you... That works perfectly. However, I think I have added a
small wrinkle. I can now rank them just fine, but is there a way to
Rank, Compare and label them as well? Let me try to outlin below...

A 100 First
B 50 Second
C 100 First

What I would like to do is write something that ranks them, but then
looks at the values, and if two or more have the same value, simply say
"Tied". So it would look like so in the next example...

A 50 Tied
B 50 Tied
C 100 First

Thanks ahead of time for any advice...

Put the original formula inside an IF that tests for that condition.

E.g.:

=IF(COUNTIF($B$1:$B$3,B1)1,"Tied",
CHOOSE(RANK(B1,$B$1:$B$3),"1st","2nd","3rd"))


or even:

=IF(COUNTIF(B:B,B1)1,"Tied for "&CHOOSE(
RANK(B1,$B$1:$B$3),"1st","2nd","3rd"),
CHOOSE(RANK(B1,$B$1:$B$3),"1st","2nd","3rd"))

or finally:

=IF(COUNTIF(B:B,B1)1,"Tied for"& RANK(B1,B:B)&
IF(AND(MOD(RANK(B1,B:B),100)=11,MOD(RANK(
B1,B:B),100)<=19),"th",IF(MOD(RANK(B1,B:B),10)=1," st",
IF(MOD(RANK(B1,B:B),10)=2,"nd",IF(MOD(RANK(
B1,B:B),10)=3,"rd","th")))),RANK(B1,B:B)&IF(AND(MO D(
RANK(B1,B:B),100)=11,MOD(RANK(B1,B:B),100)<=19)," th",
IF(MOD(RANK(B1,B:B),10)=1,"st",IF(MOD(RANK(B1,B:B) ,10)=2,
"nd",IF(MOD(RANK(B1,B:B),10)=3,"rd","th")))))


--ron

On Sat, 10 Sep 2005 14:10:13 -0400, Ron Rosenfeld
wrote:

On 10 Sep 2005 09:24:15 -0700, "stacy" wrote:

Thank you... That works perfectly. However, I think I have added a
small wrinkle. I can now rank them just fine, but is there a way to
Rank, Compare and label them as well? Let me try to outlin below...

A 100 First
B 50 Second
C 100 First

What I would like to do is write something that ranks them, but then
looks at the values, and if two or more have the same value, simply say
"Tied". So it would look like so in the next example...

A 50 Tied
B 50 Tied
C 100 First

Thanks ahead of time for any advice...

Put the original formula inside an IF that tests for that condition.

E.g.:

=IF(COUNTIF($B$1:$B$3,B1)1,"Tied",
CHOOSE(RANK(B1,$B$1:$B$3),"1st","2nd","3rd"))


or even:

=IF(COUNTIF(B:B,B1)1,"Tied for "&CHOOSE(
RANK(B1,$B$1:$B$3),"1st","2nd","3rd"),
CHOOSE(RANK(B1,$B$1:$B$3),"1st","2nd","3rd"))

or finally:

=IF(COUNTIF(B:B,B1)1,"Tied for"& RANK(B1,B:B)&
IF(AND(MOD(RANK(B1,B:B),100)=11,MOD(RANK(
B1,B:B),100)<=19),"th",IF(MOD(RANK(B1,B:B),10)=1, "st",
IF(MOD(RANK(B1,B:B),10)=2,"nd",IF(MOD(RANK(
B1,B:B),10)=3,"rd","th")))),RANK(B1,B:B)&IF(AND(M OD(
RANK(B1,B:B),100)=11,MOD(RANK(B1,B:B),100)<=19), "th",
IF(MOD(RANK(B1,B:B),10)=1,"st",IF(MOD(RANK(B1,B:B ),10)=2,
"nd",IF(MOD(RANK(B1,B:B),10)=3,"rd","th")))))


--ron


Simpler versions of above:

=IF(COUNTIF(B:B,B1)1,"Tied for ","")&
CHOOSE(RANK(B1,$B$1:$B$3),"1st","2nd","3rd")

or

=IF(COUNTIF(B:B,B1)1,"Tied for ","")&RANK(B1,B:B)&IF(AND(MOD(
RANK(B1,B:B),100)=11,MOD(RANK(B1,B:B),100)<=19)," th",
IF(MOD(RANK(B1,B:B),10)=1,"st",IF(MOD(RANK(B1,B:B) ,10)=2,
"nd",IF(MOD(RANK(B1,B:B),10)=3,"rd","th"))))


--ron

Perfect!! Thanks so much for all of your help... Works great!!

On 12 Sep 2005 09:30:57 -0700, "stacy" wrote:

Perfect!! Thanks so much for all of your help... Works great!!

You're welcome. Thank you for the feedback.
--ron