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