Average of highest 3 numbers

[chrisj2812]

Hi,

I have a vertical data set and want to find the average of the top 3 numbers (Speed Ratings) for each variable (horse), I know how to do this when the number range is horizontal, but the vertical layout is confusing me. If someone could point me in the right direction I'd be most grateful.

Many Thanks

Chris

 

[etaf]

I know how to do this when the number range is horizontal,

cant you just change the range to vertical ?
whats the formula

how about
=AVERAGE(LARGE(AV2:AV100,{1,2,3}))

change range to match you data

data-ETAF.xlsx
	A	B	C	D
1
2		1		8.33333333
3		2
4		3
5		4
6		5
7		6
8		1
9		1
10		9
11		1		8.33333333
12		10
13		1
Sheet1

Cell Formulas
Range	Formula
D2	D2	=AVERAGE(LARGE(B2:B13,{1,2,3}))
D11	D11	=AVERAGE(B7,B10,B12)

 

[Georgiboy]

If it is by horse then:

Book1
	L																																				AV	AW
1	Horse																																					Average If
2	A																																				1	34.33333333
3	A																																				2	34.33333333
4	A																																				3	34.33333333
5	A																																				4	34.33333333
6	A																																				96	34.33333333
7	B																																				1	4
8	B																																				2	4
9	B																																				3	4
10	B																																				4	4
11	B																																				5	4
12	C																																				1	3.333333333
13	C																																				2	3.333333333
14	C																																				3	3.333333333
15	C																																				5	3.333333333
Sheet1

Cell Formulas
Range	Formula
AW2:AW15	AW2	=AVERAGE(LARGE(IF($L$2:$L$15=L2,$AV$2:$AV$15),{1,2,3}))

 

[Peter_SSs]

Try this since it looks like you have a formal table structure. In your version you may possibly need to confirm formula with Ctrl+Shift+Enter

22 09 05.xlsm
	L	M	N
1	Horse	Speed Rtg	AvgSR
2	Horse 1	53	78
3	Horse 1	86	78
4	Horse 1	42	78
5	Horse 1	60	78
6	Horse 1	79	78
7	Horse 1	63	78
8	Horse 1	69	78
9	Horse 1	60	78
10	Horse 2	50	61
11	Horse 2	48	61
12	Horse 2	47	61
13	Horse 2	71	61
14	Horse 2	62	61
Avg Top 3

Cell Formulas
Range	Formula
N2:N14	N2	=AVERAGE(LARGE(IF([Horse]=[@Horse],[Speed Rtg]),{1,2,3}))

 

[Fluff]

Another option, that shouldn't need Ctrl Shift Enter

=AVERAGE(AGGREGATE(14,6,$AV$2:$AV$100/($L$2:$L$100=L2),{1,2,3}))

 

[Phuoc]

Or try

Book1
	A	B	C	D	E
1	Horse
2	A	1		A	34.33
3	A	2		B	4.00
4	A	3		C	3.33
5	A	4
6	A	96
7	B	1
8	B	2
9	B	3
10	B	4
11	B	5
12	C	1
13	C	2
14	C	3
15	C	5
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=AVERAGE(AGGREGATE(14,6,$B$2:$B$15/($A$2:$A$15=D2),{1,2,3}))

 

[chrisj2812]

Thank you so much folks, really appreciate your time, effort & knowledge, it has really helped me!

Chris

 

[Fluff]

Glad we could help & thanks for the feedback.