Average problem

[SERVECOLD]

hi not sure how to get round this in column F ive been trying to find away to find the average of the other horses but can only come up with the way that ive done it
can it be done any other way thanks

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	20/09/2011	9	11%
2	TIME	COURSE	HORSE	MST	DSLR
3	14:00:00	Beverley	Cat O' Nine Tails	2418.92	5	2142	12100	0.29	1.44	69%	2.82	1.38
4	14:00:00	Beverley	Indochina	2378.64	15	2148	35685	0.17	2.57	39%	8.31	5.74
5	14:00:00	Beverley	Valantino Oyster	2368.04	49	2150	116039	0.13	6.35	16%	26.99	20.64
6	14:00:00	Beverley	Commander Veejay	2173.00	31	2178	67368	0.14	4.45	22%	15.47	11.02
7	14:00:00	Beverley	Goodmanyourself	2064.88	56	2193	115639	0.13	7.28	14%	26.37	19.08
8	14:00:00	Beverley	Max My Boy	2033.08	51	2198	103693	0.13	6.75	15%	23.59	16.85
9	14:00:00	Beverley	Tunduce	2030.96	14	2198	28439	0.19	2.64	38%	6.47	3.83
10	14:00:00	Beverley	Sally Anne	1948.28	21	2210	40919	0.17	3.47	29%	9.26	5.79
Sheet1

Excel 2002

Cell Formulas
Range	Formula
F3	=(D4+D5+D6+D7+D8+D9+D10)/7
F4	=(D3+D5+D6+D7+D8+D9+D10)/7
F5	=(D3+D4+D6+D7+D8+D9+D10)/7
F6	=(D3+D4+D5+D7+D8+D9+D10)/7
F7	=(D3+D4+D5+D6+D8+D9+D10)/7
F8	=(D3+D4+D5+D6+D7+D9+D10)/7
F9	=(D3+D4+D5+D6+D7+D8+D10)/7
F10	=(D3+D4+D5+D6+D7+D8+D9)/7
G3	=(D3*E3)+(F3/400)
G4	=(D4*E4)+(F4/400)
G5	=(D5*E5)+(F5/400)
G6	=(D6*E6)+(F6/400)
G7	=(D7*E7)+(F7/400)
G8	=(D8*E8)+(F8/400)
G9	=(D9*E9)+(F9/400)
G10	=(D10*E10)+(F10/400)
H3	=(F3/G3)+$C$1
H4	=(F4/G4)+$C$1
H5	=(F5/G5)+$C$1
H6	=(F6/G6)+$C$1
H7	=(F7/G7)+$C$1
H8	=(F8/G8)+$C$1
H9	=(F9/G9)+$C$1
H10	=(F10/G10)+$C$1
I3	=H3*E3
I4	=H4*E4
I5	=H5*E5
I6	=H6*E6
I7	=H7*E7
I8	=H8*E8
I9	=H9*E9
I10	=H10*E10
J3	=1/I3
J4	=1/I4
J5	=1/I5
J6	=1/I6
J7	=1/I7
J8	=1/I8
J9	=1/I9
J10	=1/I10
K3	=(G3/F3)/2
K4	=(G4/F4)/2
K5	=(G5/F5)/2
K6	=(G6/F6)/2
K7	=(G7/F7)/2
K8	=(G8/F8)/2
K9	=(G9/F9)/2
K10	=(G10/F10)/2
L3	=K3-I3
L4	=K4-I4
L5	=K5-I5
L6	=K6-I6
L7	=K7-I7
L8	=K8-I8
L9	=K9-I9
L10	=K10-I10

 

[njimack]

Try the following array formula (confirm with ctrl+shift+enter), change the ranges to accomodate your full dataset:
=AVERAGE(IF($A$3:$A$10=A3,IF($B$3:$B$10=B3,IF($C$3:$C$10<>C3,$D$3:$D$10))))

 

[obiron]

F3 = (sum($D$3:$D$10) - D3)/7
F4 = (sum($D$3:$D$10) - D4)/7
F5 = (sum($D$3:$D$10) - D5)/7
...

this assumes that there are always the same number of horses in the race.

you could enhance this by replacing the range with a named range the average is the sum of the named range (less this horse) divided by the count of the named range (less 1 for this horse)

F3 = (sum(NamedRange) - D3)/(count(NamedRange)-1)
F4 = (sum(NamedRange) - D4)/(count(NamedRange)-1)

And then set the range for NamedRange using VBA (or manually)

 

[SERVECOLD]

fantastic both of you
thank you
of to try them both see which i prefer using