Excel Ranking with multiple conditions

[happyahamed]

Hi,

I have an excel file with the below format. Now i want to put rank for the event. Here I am manually put the rank (rank 1 Only) on the last field.

Please can you give the formula instead of manual?

THanks

[TABLE="width: 382"]
<tbody>[TR]
[TD]Event[/TD]
[TD]Time[/TD]
[TD]Details[/TD]
[TD]lay[/TD]
[TD]RANK[/TD]
[/TR]
[TR]
[TD]Catt 20th Oct[/TD]
[TD]13:40[/TD]
[TD]5f Nov Stks[/TD]
[TD]3.3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Catt 20th Oct[/TD]
[TD]13:40[/TD]
[TD]5f Nov Stks[/TD]
[TD]3.4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Catt 20th Oct[/TD]
[TD]13:40[/TD]
[TD]5f Nov Stks[/TD]
[TD]8.4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Catt 20th Oct[/TD]
[TD]13:40[/TD]
[TD]5f Nov Stks[/TD]
[TD]7.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Catt 20th Oct[/TD]
[TD]13:40[/TD]
[TD]5f Nov Stks[/TD]
[TD]7.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chelt 20th Oct[/TD]
[TD]14:00[/TD]
[TD]3m1f Nov Hrd[/TD]
[TD]4.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chelt 20th Oct[/TD]
[TD]14:00[/TD]
[TD]3m1f Nov Hrd[/TD]
[TD]3.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chelt 20th Oct[/TD]
[TD]14:00[/TD]
[TD]3m1f Nov Hrd[/TD]
[TD]7.8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chelt 20th Oct[/TD]
[TD]14:00[/TD]
[TD]3m1f Nov Hrd[/TD]
[TD]3.65[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Chelt 20th Oct[/TD]
[TD]14:00[/TD]
[TD]3m1f Nov Hrd[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chelt 20th Oct[/TD]
[TD]14:00[/TD]
[TD]3m1f Nov Hrd[/TD]
[TD]44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chelt 20th Oct[/TD]
[TD]14:00[/TD]
[TD]3m1f Nov Hrd[/TD]
[TD]160[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chelt 20th Oct[/TD]
[TD]14:00[/TD]
[TD]3m1f Nov Hrd[/TD]
[TD]340[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kelso 20th Oct[/TD]
[TD]14:05[/TD]
[TD]2m Mdn Hrd[/TD]
[TD]4.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kelso 20th Oct[/TD]
[TD]14:05[/TD]
[TD]2m Mdn Hrd[/TD]
[TD]6.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kelso 20th Oct[/TD]
[TD]14:05[/TD]
[TD]2m Mdn Hrd[/TD]
[TD]3.7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Kelso 20th Oct[/TD]
[TD]14:05[/TD]
[TD]2m Mdn Hrd[/TD]
[TD]9.4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kelso 20th Oct[/TD]
[TD]14:05[/TD]
[TD]2m Mdn Hrd[/TD]
[TD]7.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kelso 20th Oct[/TD]
[TD]14:05[/TD]
[TD]2m Mdn Hrd[/TD]
[TD]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kelso 20th Oct[/TD]
[TD]14:05[/TD]
[TD]2m Mdn Hrd[/TD]
[TD]110[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kelso 20th Oct[/TD]
[TD]14:05[/TD]
[TD]2m Mdn Hrd[/TD]
[TD]520[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DavidCoop]

I think this is what you want:

Excel 2010
	A	B	C	D	E
1	Event	Time	Details	lay	RANK
2	Catt 20th Oct	13:40	3.3	3.3	1
3	Catt 20th Oct	13:40	3.4	3.4
4	Catt 20th Oct	13:40	8.4	8.4
5	Catt 20th Oct	13:40	7.2	7.2
6	Catt 20th Oct	13:40	7.2	7.2
7	Chelt 20th Oct	14:00	4.2	4.2
8	Chelt 20th Oct	14:00	3.7	3.7
9	Chelt 20th Oct	14:00	7.8	7.8
10	Chelt 20th Oct	14:00	3.65	3.65	1
11	Chelt 20th Oct	14:00	20	20
12	Chelt 20th Oct	14:00	44	44
13	Chelt 20th Oct	14:00	160	160
14	Chelt 20th Oct	14:00	340	340
15	Kelso 20th Oct	14:05	4.5	4.5
16	Kelso 20th Oct	14:05	6.6	6.6
17	Kelso 20th Oct	14:05	3.7	3.7	1
18	Kelso 20th Oct	14:05	9.4	9.4
19	Kelso 20th Oct	14:05	7.6	7.6
20	Kelso 20th Oct	14:05	11	11
21	Kelso 20th Oct	14:05	110	110
22	Kelso 20th Oct	14:05	520	520
Sheet2

Cell Formulas
Range	Formula
E2	{=IF(D2=MIN(IF(--($A$2:$A$22=A2)>0,$D$2:$D$22)),1,"")}
E3	{=IF(D3=MIN(IF(--($A$2:$A$22=A3)>0,$D$2:$D$22)),1,"")}
E4	{=IF(D4=MIN(IF(--($A$2:$A$22=A4)>0,$D$2:$D$22)),1,"")}
E5	{=IF(D5=MIN(IF(--($A$2:$A$22=A5)>0,$D$2:$D$22)),1,"")}
E6	{=IF(D6=MIN(IF(--($A$2:$A$22=A6)>0,$D$2:$D$22)),1,"")}
E7	{=IF(D7=MIN(IF(--($A$2:$A$22=A7)>0,$D$2:$D$22)),1,"")}
E8	{=IF(D8=MIN(IF(--($A$2:$A$22=A8)>0,$D$2:$D$22)),1,"")}
E9	{=IF(D9=MIN(IF(--($A$2:$A$22=A9)>0,$D$2:$D$22)),1,"")}
E10	{=IF(D10=MIN(IF(--($A$2:$A$22=A10)>0,$D$2:$D$22)),1,"")}
E11	{=IF(D11=MIN(IF(--($A$2:$A$22=A11)>0,$D$2:$D$22)),1,"")}
E12	{=IF(D12=MIN(IF(--($A$2:$A$22=A12)>0,$D$2:$D$22)),1,"")}
E13	{=IF(D13=MIN(IF(--($A$2:$A$22=A13)>0,$D$2:$D$22)),1,"")}
E14	{=IF(D14=MIN(IF(--($A$2:$A$22=A14)>0,$D$2:$D$22)),1,"")}
E15	{=IF(D15=MIN(IF(--($A$2:$A$22=A15)>0,$D$2:$D$22)),1,"")}
E16	{=IF(D16=MIN(IF(--($A$2:$A$22=A16)>0,$D$2:$D$22)),1,"")}
E17	{=IF(D17=MIN(IF(--($A$2:$A$22=A17)>0,$D$2:$D$22)),1,"")}
E18	{=IF(D18=MIN(IF(--($A$2:$A$22=A18)>0,$D$2:$D$22)),1,"")}
E19	{=IF(D19=MIN(IF(--($A$2:$A$22=A19)>0,$D$2:$D$22)),1,"")}
E20	{=IF(D20=MIN(IF(--($A$2:$A$22=A20)>0,$D$2:$D$22)),1,"")}
E21	{=IF(D21=MIN(IF(--($A$2:$A$22=A21)>0,$D$2:$D$22)),1,"")}
E22	{=IF(D22=MIN(IF(--($A$2:$A$22=A22)>0,$D$2:$D$22)),1,"")}
Press CTRL+SHIFT+ENTER to enter array formulas.