Average Top 10 Values Only

[ExcelRaceRatings]

Hi All,

I have a large spreadsheet containing race times from Horse Racing tracks all around Australia. I want to able to average the top 10 times sorted by: track, distance and track condition.

Using "Flem 1200 4" as an example (i use the concatenate formula to join multiple data into one column). There are 62 entries with Flem 1200 4 in A1:A62 and the race times in B1:B62. As stated above i want to be able to Average the top 10 values which fall under "Flem 1200 4" only. I need to do this for 11,000 different types of scenarios hence why i would love a formula to be able to pick the top 10 from each scenario with ease?

Would greatly appreciate any assistance!

 

[Aladin Akyurek]

Would you care to post a scaled-down sample for a top 3 times?

 

[ExcelRaceRatings]

Would you care to post a scaled-down sample for a top 3 times?

Hi there,

Below is a small smaple of what i require. Remember there are 62 values all up in Column A under "Flem 1200 4". I am after simply an AVG of the top 10 only is possible?

TRACKS	TIMES		AVG TOP 10	AVG TIME T10
FLEM 1200 4 <tbody> </tbody>	69.01		FLEM 1200 4 <tbody> </tbody>	FORMULA REQUIRED
FLEM 1200 4 <tbody> </tbody>	69.08		FLEM 1200 5
FLEM 1200 4 <tbody> </tbody>	69.17		FLEM 1200 6
FLEM 1200 4 <tbody> </tbody>	69.26		FLEM 1200 7
FLEM 1200 4 <tbody> </tbody>	69.32		FLEM 1200 8
FLEM 1200 4 <tbody> </tbody>	69.38		FLEM 1200 9
FLEM 1200 4 <tbody> </tbody>	69.52		FLEM 1200 10
FLEM 1200 4 <tbody> </tbody>	69.55		FLEM 1400 2
FLEM 1200 4 <tbody> </tbody>	69.59		FLEM 1400 3
FLEM 1200 4 <tbody> </tbody>	69.69		FLEM 1400 4
FLEM 1200 4 <tbody> </tbody>	69.71		FLEM 1400 5

<tbody>
</tbody>

Thank you

 

[Chris Chua]

Hi RaceRatings,

I'm not sure if I get your question correctly, but you can use the LARGE function.

Excel 2012
	A	B	C	D	E
1	ID	Time		Top 10
2	Flem 1200 4	0.543218		0.981621	#1
3	Flem 1200 4	0.193337		0.971097	#2
4	Flem 1200 4	0.940597		0.956435	#3
5	Flem 1200 4	0.9023		0.940597	#4
6	Flem 1200 4	0.509783		0.916657	#5
7	Flem 1200 4	0.203545		0.916005	#6
8	Flem 1200 4	0.382782		0.9023	#7
9	Flem 1200 4	0.160425		0.883813	#8
10	Flem 1200 4	0.270349		0.852557	#9
11	Flem 1200 4	0.831837		0.831837	#10
12	Flem 1200 4	0.469737		0.915292	AVERAGE
13	Flem 1200 4	0.504709
14	Flem 1200 4	0.08148
15	Flem 1200 4	0.65257
16	Flem 1200 4	0.614582
17	Flem 1200 4	0.046653
18	Flem 1200 4	0.956435
19	Flem 1200 4	0.336515
20	Flem 1200 4	0.347901
21	Flem 1200 4	0.852557
22	Flem 1200 4	0.244155
Sheet1

Cell Formulas
Range	Formula
D2	=LARGE(Table1[Time],1)
D3	=LARGE(Table1[Time],2)
D4	=LARGE(Table1[Time],3)
D5	=LARGE(Table1[Time],4)
D6	=LARGE(Table1[Time],5)
D7	=LARGE(Table1[Time],6)
D8	=LARGE(Table1[Time],7)
D9	=LARGE(Table1[Time],8)
D10	=LARGE(Table1[Time],9)
D11	=LARGE(Table1[Time],10)
D12	=AVERAGE(D2:D11)

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​
1​	TRACKS	TIMES		AVG TOP 10	AVG TIME T10
2​	FLEM 1200 4	69.01		FLEM 1200 4	69.427
3​	FLEM 1200 4	69.08		FLEM 1200 5
4​	FLEM 1200 4	69.17		FLEM 1200 6
5​	FLEM 1200 4	69.26		FLEM 1200 7
6​	FLEM 1200 4	69.32		FLEM 1200 8
7​	FLEM 1200 4	69.38		FLEM 1200 9
8​	FLEM 1200 4	69.52		FLEM 1200 10
9​	FLEM 1200 4	69.55		FLEM 1400 2
10​	FLEM 1200 4	69.59		FLEM 1400 3
11​	FLEM 1200 4	69.69		FLEM 1400 4
12​	FLEM 1200 4	69.71		FLEM 1400 5

In E2 control+shift+enter, not just enter:

=AVERAGE(LARGE(IF($A$2:$A$12=$D2,$B$2:$B$12),
     ROW(INDIRECT("1:"&MIN(10,COUNTIFS($A$2:$A$12,$D2))))))