Multi Rank using Text

[john.battye]

Hi,

I want to be able to rank John for each Subject i.e English 3rd, Maths 3rd, Science 4th, Music 4th, History 1st.

I've looked at Rank, but can't use this. is there a formula that I can use and copy across Horzontally?

John's Rank 0f 6
Student	English	Maths	Science	Music	History
Mike	94	99	81	65	89
Steve	90	89	65	55	87
Dave	89	84	66	61	65
John	92	83	77	63	94
Phil	72	82	99	84	93
Mark	97	77	84	89	67

Any help would be much appreciated.

 

[KOKOSEK]

Book1
	C	D	E	F	G	H
1	John	3	4	4	4	1
2
3	Student	English	Maths	Science	Music	History
4	Mike	94	99	81	65	89
5	Steve	90	89	65	55	87
6	Dave	89	84	66	61	65
7	John	92	83	77	63	94
8	Phil	72	82	99	84	93
9	Mark	97	77	84	89	67
Sheet1

Cell Formulas
Range	Formula
D1:H1	D1	=RANK.EQ(VLOOKUP($C$1,$C$4:D$9,COLUMNS($A$1:B1),0),D$4:D$9)

 

[ebea]

You can copy down, and across with this: =RANK(B2;B$2:B$7)

 

[john.battye]

You can copy down, and across with this: =RANK(B2;B$2:B$7)

Thanks Kokosek, ebea. This is working for me now. It took me a few mins to workout that the COLUMNS function was just an efficient way of giving me the correct column number, but got it now.

Just noticed ebea that you joined 6 days before me in 2008.