Applying points to ranked cells

[llcamino]

I'm trying to rank a group of 5 cells and assign a value to them. For example Cells B26:B30 I would like to assign the values as follows in Cells C26:C30.

Numbers in Column B must stay in their current cell.

Lowest Number from Column B receives a 10 in Column C
Next lowest in B gets 7 in C
5 in C
3 in C
1 in C

Is this possible?

Thanks in advance.

 

[Bubbis Thedog]

Not to step on Brian's toes, but the AVERAGE() function needs to be entered as an array formula (hence the curly braces). To do this press at the same time, CTRL+SHIFT+ENTER, once the formula is entered. If you copy the formula from Brian's sheet, you need to delete the curlys, and follow the array formula-entering process that I just mentioned.

Bubbis

 

[llcamino]

Unfortunately that forumla is not working. I think it may not work because the scores in column A are not necessarily going to sorted in order of highest to lowest.

Any other suggestions?

 

[Brian from Maui]

What follows is something "borrowed" from Aladin. Taking the top nth score.

Book2
	A	B	C	D	E	F	G	H	I
2	Name	Score	Rank	R-Rank	5	Name	Rank
3	Smitty	81	5	5		Anne	1	10	10
4	Dan	75	2	2		Dan	2	7	7
5	Aladin	83	6	6		Zack	3	4	5
6	Zack	76	3	3		Scott	3	4	3
7	Anne	72	1	1		Smitty	5	1	1
8	Scott	76	3	4
Sheet1

Formula in C3 and copied down

=IF(N(B3),RANK(B3,$B$3:$B$8,1),"")

Formula in D3 and copied down

=IF(N(B3),RANK(B3,$B$3:$B$8,1)+COUNTIF($B$3:B3,B3)-1,"")

E5 houses the number to rank

Formula in F5 and copied down

=IF(ROW()-ROW(F$3)+1<=$E$2,INDEX(A:A,MATCH(ROW()-ROW(F$3)+1,$D:$D,0)),"")

Formula in G3 and copied down

=IF(ROW()-ROW(G$3)+1<=$E$2,INDEX(C:C,MATCH(ROW()-ROW(G$3)+1,$D:$D,0)),"")

Formula in H3 array entered CTRL+SHIFT+ENTER and copied down

=AVERAGE(IF($G$3:$G$7=G3,$I$3:$I$7,""))

I3:I7 housing points