Dynamic Average formua required

[Monkman]

trying to get a dynamic formula for an average for each change in Date in col A
Col D is R# which can be from 1 to 25 for a particular date.
Col BC is BP this is what I am trying to average for the different race #'s
For Apr 13 R#1 has a BP of .239437 R#2 has a BP of .239168
the avg of those two is .239302 in Col BD17

For April 14 R#1-3 has BP avg of .242773 in Col Bd 40

For April 19 R#1-6 has a BP avg of .243211 in col BD88

So the formula should acknowledge the change is DATE in col A and the different R#'s for each date.
Any help appreciated
Monk


Excel 2007
A D K N BB BC BD BE BF
1 DATE R# 1Q Final Time BP Bpavg
2 13-Apr-09 1 27.2 113.6 0.239437
3 13-Apr-09 1 27.2 113.6 0.239437
4 13-Apr-09 1 27.2 113.6 0.239437
5 13-Apr-09 1 27.2 113.6 0.239437
6 13-Apr-09 1 27.2 113.6 0.239437
7 13-Apr-09 1 27.2 113.6 0.239437
8 13-Apr-09 1 27.2 113.6 0.239437
9 13-Apr-09 1 27.2 113.6 0.239437
10 13-Apr-09 2 27.6 115.4 0.239168 change of R#
11 13-Apr-09 2 27.6 115.4 0.239168
12 13-Apr-09 2 27.6 115.4 0.239168
13 13-Apr-09 2 27.6 115.4 0.239168
14 13-Apr-09 2 27.6 115.4 0.239168
15 13-Apr-09 2 27.6 115.4 0.239168
16 13-Apr-09 2 27.6 115.4 0.239168
17 13-Apr-09 2 27.6 115.4 0.239168 0.239302 average of R#1,R#2 for Apr 13
18 14-Apr-09 1 28.8 118.6 0.242833 change of R# change of date
19 14-Apr-09 1 28.8 118.6 0.242833
20 14-Apr-09 1 28.8 118.6 0.242833
21 14-Apr-09 1 28.8 118.6 0.242833
22 14-Apr-09 1 28.8 118.6 0.242833
23 14-Apr-09 1 28.8 118.6 0.242833
24 14-Apr-09 1 28.8 118.6 0.242833
25 14-Apr-09 1 28.8 118.6 0.242833
26 14-Apr-09 2 28 116 0.241379 change of R#
27 14-Apr-09 2 28 116 0.241379
28 14-Apr-09 2 28 116 0.241379
29 14-Apr-09 2 28 116 0.241379
30 14-Apr-09 2 28 116 0.241379
31 14-Apr-09 2 28 116 0.241379
32 14-Apr-09 2 28 116 0.241379
33 14-Apr-09 2 28 116 0.241379
34 14-Apr-09 3 29 118.8 0.244108 change of R#
35 14-Apr-09 3 29 118.8 0.244108
36 14-Apr-09 3 29 118.8 0.244108
37 14-Apr-09 3 29 118.8 0.244108
38 14-Apr-09 3 29 118.8 0.244108
39 14-Apr-09 3 29 118.8 0.244108
40 14-Apr-09 3 29 118.8 0.244108 0.242773 average of R#1,R#2,R#3 *for Apr 14
41 19-Apr-09 1 27.4 112.4 0.243772 change of R# change of date
42 19-Apr-09 1 27.4 112.4 0.243772
43 19-Apr-09 1 27.4 112.4 0.243772
44 19-Apr-09 1 27.4 112.4 0.243772
45 19-Apr-09 1 27.4 112.4 0.243772
46 19-Apr-09 1 27.4 112.4 0.243772
47 19-Apr-09 1 27.4 112.4 0.243772
48 19-Apr-09 1 27.4 112.4 0.243772
49 19-Apr-09 2 27.2 113.6 0.239437 change of R#
50 19-Apr-09 2 27.2 113.6 0.239437
51 19-Apr-09 2 27.2 113.6 0.239437
52 19-Apr-09 2 27.2 113.6 0.239437
53 19-Apr-09 2 27.2 113.6 0.239437
54 19-Apr-09 2 27.2 113.6 0.239437
55 19-Apr-09 2 27.2 113.6 0.239437
56 19-Apr-09 2 27.2 113.6 0.239437
57 19-Apr-09 3 28.6 113.2 0.25265 change of R#
58 19-Apr-09 3 28.6 113.2 0.25265
59 19-Apr-09 3 28.6 113.2 0.25265
60 19-Apr-09 3 28.6 113.2 0.25265
61 19-Apr-09 3 28.6 113.2 0.25265
62 19-Apr-09 3 28.6 113.2 0.25265
63 19-Apr-09 3 28.6 113.2 0.25265
64 19-Apr-09 3 28.6 113.2 0.25265
65 19-Apr-09 4 27.4 113.4 0.241623 change of R#
66 19-Apr-09 4 27.4 113.4 0.241623
67 19-Apr-09 4 27.4 113.4 0.241623
68 19-Apr-09 4 27.4 113.4 0.241623
69 19-Apr-09 4 27.4 113.4 0.241623
70 19-Apr-09 4 27.4 113.4 0.241623
71 19-Apr-09 4 27.4 113.4 0.241623
72 19-Apr-09 4 27.4 113.4 0.241623
73 19-Apr-09 5 27.2 112.2 0.242424 change of R#
74 19-Apr-09 5 27.2 112.2 0.242424
75 19-Apr-09 5 27.2 112.2 0.242424
76 19-Apr-09 5 27.2 112.2 0.242424
77 19-Apr-09 5 27.2 112.2 0.242424
78 19-Apr-09 5 27.2 112.2 0.242424
79 19-Apr-09 5 27.2 112.2 0.242424
80 19-Apr-09 5 27.2 112.2 0.242424
81 19-Apr-09 6 27 112.8 0.239362 change of R#
82 19-Apr-09 6 27 112.8 0.239362
83 19-Apr-09 6 27 112.8 0.239362
84 19-Apr-09 6 27 112.8 0.239362
85 19-Apr-09 6 27 112.8 0.239362
86 19-Apr-09 6 27 112.8 0.239362
87 19-Apr-09 6 27 112.8 0.239362
88 19-Apr-09 6 27 112.8 0.239362 0.243211 average of R#1thru R#6 *for Apr 19
89 20-Apr-09 1

 

[Gerald Higgins]

Are there always exactly 8 entries per data / R # combination ?

 

[Monkman]

Are there always exactly 8 entries per data / R # combination ?

No there can be 16 entries for 1 r#.
there maybe be up to 25 r#'s for a specific date
Thanks for asking.
Monk