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