Formula - rank numbers high to low

[psrs0810]

I have a listing of values and I need to place a number 1-10 for the top 10 highest numbers.
For example, out of these 16 numbers I need a 1 next to the highest number, a 2 next to the second highest number, etc.

[TABLE="width: 54"]
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2560;"> <tbody>[TR]
[TD="width: 72, bgcolor: transparent, align: right"]10,527.65[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5,655.68[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1,701.93[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1,349.18[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]0.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]573.11[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]140.86[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6,064.77[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2,093.60[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5,680.65[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4,045.40[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5,546.32[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]-1,033.02[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]-277.55[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]891.20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]25,699.12[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Thats just a RANK formula:

Book1
	A	B
1	Score	Rank
2	10,527.65	2
3	5,655.68	5
4	1,701.93	9
5	1,349.18	10
6	0	14
7	573.11	12
8	140.86	13
9	6,064.77	3
10	2,093.60	8
11	5,680.65	4
12	4,045.40	7
13	5,546.32	6
14	-1,033.02	16
15	-277.55	15
16	891.2	11
17	25,699.12	1
Sheet9

Cell Formulas
Range	Formula
B2	=RANK(A2,$A$2:$A$17)

 

[Fluff]

How about

Book1
	A	B
2	10,527.65	2
3	5,655.68	5
4	1,701.93	9
5	1,349.18	10
6	0
7	573.11
8	140.86
9	6,064.77	3
10	2,093.60	8
11	5,680.65	4
12	4,045.40	7
13	5,546.32	6
14	-1,033.02
15	-277.55
16	891.2
17	25,699.12	1
Database

Cell Formulas
Range	Formula
B2	=IF(RANK(A2,$A$2:$A$17,0)>10,"",RANK(A2,$A$2:$A$17,0))

 

[psrs0810]

Thanks - it's Friday afternoon for me

 

[DanteAmor]

delete.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[psrs0810]

what if I want to make my Rank formula dynamic?
if I have 12 months of data and I have a cell that shows a particular month, I want to rank that month. but if I change the month, how do I have my formula automatically change columns?
Thanks

 

[Eric W]

How is your data laid out? Do you have 12 columns, one per month? Or do you have 2 columns, 1 with the score, 1 with the month? Or something else?

 

[psrs0810]

12 columns, one per month

 

[Eric W]

I still have some doubts about how the layout looks, but consider:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Mar		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	7		10,527.65	33.02	397.59	726.65	774.09	474.69	246.65	836.6	449.46	203.91	389.42	523.63
3	10		5,655.68	984.81	262.95	83.73	900.67	19.19	482.91	559.24	468.9	404.86	160.57	136.65
4			1,701.93	69.4	35.81	203.12	218.46	772.52	434.61	188.19	958.34	24.59	695.02	771.91
5	2		1,349.18	614.72	950.98	766.8	981.78	131.79	538.82	330.68	793.99	847.16	262.71	872.69
6	6		0	76.15	413.53	284.75	638.54	283.53	976.48	433.07	322.04	29.44	220.76	416.61
7			573.11	338.79	13.25	450.71	636.39	543.66	975.36	771.65	533.66	589.05	825.8	150.93
8	1		140.86	198.82	952.19	557.07	936.01	837.69	546.78	455.62	223.74	280.92	173.59	518
9			6,064.77	135.29	50.27	327.2	460.31	404.06	987.36	79.87	792.82	544.91	947.09	427.8
10	3		2,093.60	622.32	576.97	658.59	758.6	643.79	693.28	875.44	811.33	295.23	266.29	722.26
11	8		5,680.65	330.64	333.99	704.61	111.31	912.58	5.21	889.75	8.16	501.19	512.05	490.85
12			4,045.40	523.42	261.19	806.14	367.15	500.4	774.38	394.1	443.63	869.15	901.21	78.02
13	9		5,546.32	420.04	300.1	111.74	677.75	961.01	612.94	664.88	800.59	180.09	795.51	519.39
14	5		-1,033.02	78.36	501.24	662.54	819.88	984.94	921.43	656.52	167.21	311.72	620.68	542.24
15			-277.55	38.58	122.45	281.75	128	674.96	953.14	491.62	672.53	962.91	610.27	121.96
16			891.2	982.87	36.23	428.06	224.24	818.94	306.01	577.64	369.24	475.35	173.87	805.23
17	4		25,699.12	77.36	532.22	217.06	376.79	799.09	436.24	680.11	650.94	650.54	809.22	62.46
Sheet1

Cell Formulas
Range	Formula
A2:A17	A2	=IF(RANK(INDEX(C$2:N$17,ROWS(A$2:A2),MATCH(A$1,C$1:N$1,0)),INDEX(C$2:N$17,0,MATCH(A$1,C$1:N$1,0)))>10,"",RANK(INDEX(C$2:N$17,ROWS(A$2:A2),MATCH(A$1,C$1:N$1,0)),INDEX(C$2:N$17,0,MATCH(A$1,C$1:N$1,0))))

By changing the value in A1, it will rank the values from the corresponding column.