Compare Multiple Numeric Values

[stacy]

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!!

 

[Guest]

Use CHOOSE()


In A1, A2, A3 put:
=CHOOSE((B1>B2)+(B1>B3)+1,"3rd","2nd","1st")
=CHOOSE((B2>B3)+(B2>B1)+1,"3rd","2nd","1st")
=CHOOSE((B3>B1)+(B3>B2)+1,"3rd","2nd","1st")

 

[Ron Rosenfeld]

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

 

[stacy]

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

 

[Ron Rosenfeld]

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(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

 

[Ron Rosenfeld]

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(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

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