Help with Ranking?

[MichaelRSnow]

Is it possible to rank based on 2 criteria, TEXT and VALUE. i.e. the table list as illustrated below, rank based on columns B and C. So rank any with a value of X only against X's, rank any with a value of Y only against Y's and so?</SPAN>
[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D- RANK (COLUMNS B&C)[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X[/TD]
[TD]0.5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Z[/TD]
[TD]1.5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]X[/TD]
[TD]0.25[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Z[/TD]
[TD]1.5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]X[/TD]
[TD]0.85[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Y[/TD]
[TD]2.25[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Z[/TD]
[TD]1.25[/TD]
[TD]1[/TD]
[/TR]
</TBODY>[/TABLE]

 

[FormR]

Hi, try:

Excel Workbook
	A	B	C	D
1	A	B	C	D- RANK (COLUMNS B&C)
2	A	X	0.5	2
3	A	Y	1	1
4	A	Z	1.5	2
5	B	X	0.25	1
6	B	Y	2	2
7	B	Z	1.5	2
8	C	X	0.85	3
9	C	Y	2.25	3
10	C	Z	1.25	1
Sheet1

 

[snb_]

Maybe the array formula:

=RANK.EQ($C$2,($B$2:$B$10=$B$2)*$C$2:$C$10)

 

[FormR]

Maybe the array formula:

This doesn't work for me using XL2010.

 

[MichaelRSnow]

Hi, try:

Sheet1

*	A	B	C	D
A	B	C	D- RANK (COLUMNS B&C)
A	X
A	Y
A	Z
B	X
B	Y
B	Z
C	X
C	Y
C	Z

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]0.5[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]1.5[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]0.25[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]1.5[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]0.85[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]2.25[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]1.25[/TD]
[TD="align: right"]1[/TD]

</TBODY>

Spreadsheet Formulas

Cell Formula D2 =COUNTIFS($B$2:$B$10,B2,$C$2:$C$10,"<"&C2)+1 <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks, works perfectly