Get values from specific date ranges

[mrbeige]

Hi, I'm having trouble creating a formula to calculate specific ranges of data.

Raw data :
I have a range of 5000+ dates (column A) and associated values (column B).
The dates span from about 2000 to today. There are about 20 dates per month for each month (the gaps correspond to weekends). The dates are in chronological order.
I also have a secondary dataset consisting of a series of dates.


My goal is to calculate the average of values found in column B for the 12 months preceding the date given in my second dataset.

So, for example, given that my first date is 2006-12-01, I can manually find every value from column B between 2005-12-01 and 2006-11-30 and calculate the average, but now I would like to automatize this process.

I've been looking at INDEX/MATCH functions but I'm out of my depth to be completely frank.

 

[kevin9999]

Is this what you mean?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Value
2	2001-01-02	5.03
3	2001-01-03	5.57			Starting date
4	2001-01-04	5.33				2006-12-01	2007-12-01	2008-12-01	2009-12-01	2010-12-01	2011-12-01	2012-12-01	2013-12-01
5	2001-01-05	5.08				5.49	5.52	5.50	5.49	5.48	5.49	5.47	5.50
6	2001-01-06	5.28
7	2001-01-07	5.80
8	2001-01-08	5.93
9	2001-01-09	5.99
10	2001-01-10	5.37
Sheet1

Cell Formulas
Range	Formula
G4:M4	G4	=EDATE(F4,12)
F5:M5	F5	=AVERAGEIFS($B:$B,$A:$A,"<="&F4,$A:$A,">="&EDATE(F4,-12))

 

[mrbeige]

So it's hard to give the proper context considering how many values I have but here is an example of my calculations for the first date.

You have to imagine that the data in columns A and B in my screenshot go down for 5000+ rows and I need to retrieve a range similar to the one I'm showing here. Also, being an excel noob, I don't really understand your formula. I'll have to test it to see how it goes.

2006-12-01
	4,21
Values I need
2005-12-01	4,05
2005-12-02	4,08
2005-12-05	4,11
2005-12-06	4,05
2005-12-07	4,07
2005-12-08	4,04
2005-12-09	4,11
2005-12-12	4,13
2005-12-13	4,12
2005-12-14	4,08
2005-12-15	4,06
2005-12-16	4,01
2005-12-19	4
2005-12-20	4,02
2005-12-21	4,03
2005-12-22	4
2005-12-23	3,99
2005-12-28	3,93
2005-12-29	3,96
2005-12-30	3,98
2006-01-03	3,97
2006-01-04	3,93
2006-01-05	3,95
2006-01-06	3,97
2006-01-09	3,99
2006-01-10	4,03
2006-01-11	4,05
2006-01-12	4,03
2006-01-13	3,99
2006-01-16	3,98
2006-01-17	3,94
2006-01-18	3,94
2006-01-19	4
2006-01-20	4,01
2006-01-23	4,04
2006-01-24	4,05
2006-01-25	4,11
2006-01-26	4,14
2006-01-27	4,15
2006-01-30	4,17
2006-01-31	4,17
2006-02-01	4,18
2006-02-02	4,18
2006-02-03	4,16
2006-02-06	4,21
2006-02-07	4,2
2006-02-08	4,21
2006-02-09	4,21
2006-02-10	4,22
2006-02-13	4,2
2006-02-14	4,22
2006-02-15	4,21
2006-02-16	4,19
2006-02-17	4,12
2006-02-20	4,11
2006-02-21	4,13
2006-02-22	4,1
2006-02-23	4,13
2006-02-24	4,15
2006-02-27	4,15
2006-02-28	4,12
2006-03-01	4,15
2006-03-02	4,18
2006-03-03	4,22
2006-03-06	4,27
2006-03-07	4,22
2006-03-08	4,2
2006-03-09	4,21
2006-03-10	4,24
2006-03-13	4,25
2006-03-14	4,18
2006-03-15	4,19
2006-03-16	4,12
2006-03-17	4,14
2006-03-20	4,16
2006-03-21	4,2
2006-03-22	4,19
2006-03-23	4,21
2006-03-24	4,13
2006-03-27	4,16
2006-03-28	4,21
2006-03-29	4,23
2006-03-30	4,27
2006-03-31	4,26
2006-04-03	4,29
2006-04-04	4,32
2006-04-05	4,32
2006-04-06	4,35
2006-04-07	4,41
2006-04-10	4,4
2006-04-11	4,38
2006-04-12	4,4
2006-04-13	4,44
2006-04-17	4,42
2006-04-18	4,4
2006-04-19	4,44
2006-04-20	4,48
2006-04-21	4,45
2006-04-24	4,42
2006-04-25	4,5
2006-04-26	4,52
2006-04-27	4,5
2006-04-28	4,46
2006-05-01	4,51
2006-05-02	4,48
2006-05-03	4,48
2006-05-04	4,47
2006-05-05	4,45
2006-05-08	4,45
2006-05-09	4,46
2006-05-10	4,47
2006-05-11	4,46
2006-05-12	4,45
2006-05-15	4,41
2006-05-16	4,37
2006-05-17	4,38
2006-05-18	4,31
2006-05-19	4,33
2006-05-23	4,32
2006-05-24	4,3
2006-05-25	4,29
2006-05-26	4,3
2006-05-29	4,3
2006-05-30	4,35
2006-05-31	4,45
2006-06-01	4,39
2006-06-02	4,31
2006-06-05	4,31
2006-06-06	4,29
2006-06-07	4,31
2006-06-08	4,32
2006-06-09	4,37
2006-06-12	4,35
2006-06-13	4,31
2006-06-14	4,35
2006-06-15	4,39
2006-06-16	4,39
2006-06-19	4,41
2006-06-20	4,49
2006-06-21	4,52
2006-06-22	4,56
2006-06-23	4,61
2006-06-26	4,64
2006-06-27	4,6
2006-06-28	4,63
2006-06-29	4,58
2006-06-30	4,58
2006-07-04	4,6
2006-07-05	4,61
2006-07-06	4,59
2006-07-07	4,49
2006-07-10	4,46
2006-07-11	4,44
2006-07-12	4,46
2006-07-13	4,45
2006-07-14	4,44
2006-07-17	4,44
2006-07-18	4,49
2006-07-19	4,43
2006-07-20	4,42
2006-07-21	4,38
2006-07-24	4,37
2006-07-25	4,4
2006-07-26	4,38
2006-07-27	4,38
2006-07-28	4,34
2006-07-31	4,31
2006-08-01	4,31
2006-08-02	4,32
2006-08-03	4,33
2006-08-04	4,3
2006-08-08	4,28
2006-08-09	4,32
2006-08-10	4,33
2006-08-11	4,36
2006-08-14	4,38
2006-08-15	4,32
2006-08-16	4,28
2006-08-17	4,26
2006-08-18	4,22
2006-08-21	4,21
2006-08-22	4,19
2006-08-23	4,2
2006-08-24	4,2
2006-08-25	4,18
2006-08-28	4,18
2006-08-29	4,16
2006-08-30	4,12
2006-08-31	4,11
2006-09-01	4,08
2006-09-04	4,08
2006-09-05	4,14
2006-09-06	4,14
2006-09-07	4,13
2006-09-08	4,1
2006-09-11	4,15
2006-09-12	4,11
2006-09-13	4,12
2006-09-14	4,13
2006-09-15	4,1
2006-09-18	4,12
2006-09-19	4,08
2006-09-20	4,08
2006-09-21	4,02
2006-09-22	3,99
2006-09-25	3,93
2006-09-26	3,97
2006-09-27	3,98
2006-09-28	3,99
2006-09-29	4
2006-10-02	3,98
2006-10-03	3,99
2006-10-04	3,96
2006-10-05	4,01
2006-10-06	4,08
2006-10-10	4,11
2006-10-11	4,13
2006-10-12	4,14
2006-10-13	4,17
2006-10-16	4,15
2006-10-17	4,17
2006-10-18	4,15
2006-10-19	4,18
2006-10-20	4,18
2006-10-23	4,2
2006-10-24	4,2
2006-10-25	4,17
2006-10-26	4,11
2006-10-27	4,07
2006-10-30	4,07
2006-10-31	4,02
2006-11-01	3,98
2006-11-02	4,02
2006-11-03	4,11
2006-11-06	4,1
2006-11-07	4,05
2006-11-08	4,04
2006-11-09	4,03
2006-11-10	4
2006-11-14	3,98
2006-11-15	4,02
2006-11-16	4,03
2006-11-17	4
2006-11-20	4
2006-11-21	3,99
2006-11-22	3,98
2006-11-23	3,98
2006-11-24	3,97
2006-11-27	3,95
2006-11-28	3,92
2006-11-29	3,94
2006-11-30	3,9

 

[kevin9999]

I note that your layout in post #3 is different from your original post. Always best to post your actual layout initially. The formula works like this:
=AVERAGEIFS(<range to average>,<criteria range 1>,<criteria 1,<criteria range 2>,<criteria 2>) and you can keep on adding all the criteria you're ever likely to need.
So...

=AVERAGEIFS(B4:B5005,A4:A5005,"<="&A1,A4:A5005,">="&EDATE(A1,-12))

EDATE gets the date 12 months prior to the date in A1 (-12)

Book1
	A	B
1	2006-12-01
2		4.21
3	Values I need
4	2005-12-01	4.05
5	2005-12-02	4.08
6	2005-12-05	4.11
7	2005-12-06	4.05
8	2005-12-07	4.07
9	2005-12-08	4.04
10	2005-12-09	4.11
11	2005-12-12	4.13
12	2005-12-13	4.12
13	2005-12-14	4.08
14	2005-12-15	4.06
15	2005-12-16	4.01
16	2005-12-19	4
17	2005-12-20	4.02
18	2005-12-21	4.03
19	2005-12-22	4
20	2005-12-23	3.99
21	2005-12-28	3.93
22	2005-12-29	3.96
23	2005-12-30	3.98
24	2006-01-03	3.97
25	2006-01-04	3.93
26	2006-01-05	3.95
27	2006-01-06	3.97
28	2006-01-09	3.99
29	2006-01-10	4.03
30	2006-01-11	4.05
31	2006-01-12	4.03
32	2006-01-13	3.99
33	2006-01-16	3.98
34	2006-01-17	3.94
35	2006-01-18	3.94
36	2006-01-19	4
37	2006-01-20	4.01
38	2006-01-23	4.04
39	2006-01-24	4.05
40	2006-01-25	4.11
41	2006-01-26	4.14
42	2006-01-27	4.15
43	2006-01-30	4.17
44	2006-01-31	4.17
45	2006-02-01	4.18
46	2006-02-02	4.18
47	2006-02-03	4.16
48	2006-02-06	4.21
49	2006-02-07	4.2
50	2006-02-08	4.21
51	2006-02-09	4.21
52	2006-02-10	4.22
53	2006-02-13	4.2
54	2006-02-14	4.22
55	2006-02-15	4.21
56	2006-02-16	4.19
57	2006-02-17	4.12
58	2006-02-20	4.11
59	2006-02-21	4.13
60	2006-02-22	4.1
61	2006-02-23	4.13
62	2006-02-24	4.15
63	2006-02-27	4.15
64	2006-02-28	4.12
65	2006-03-01	4.15
66	2006-03-02	4.18
67	2006-03-03	4.22
68	2006-03-06	4.27
69	2006-03-07	4.22
70	2006-03-08	4.2
71	2006-03-09	4.21
72	2006-03-10	4.24
73	2006-03-13	4.25
74	2006-03-14	4.18
75	2006-03-15	4.19
76	2006-03-16	4.12
77	2006-03-17	4.14
78	2006-03-20	4.16
79	2006-03-21	4.2
80	2006-03-22	4.19
81	2006-03-23	4.21
82	2006-03-24	4.13
83	2006-03-27	4.16
84	2006-03-28	4.21
85	2006-03-29	4.23
86	2006-03-30	4.27
87	2006-03-31	4.26
88	2006-04-03	4.29
89	2006-04-04	4.32
90	2006-04-05	4.32
91	2006-04-06	4.35
92	2006-04-07	4.41
93	2006-04-10	4.4
94	2006-04-11	4.38
95	2006-04-12	4.4
96	2006-04-13	4.44
97	2006-04-17	4.42
98	2006-04-18	4.4
99	2006-04-19	4.44
100	2006-04-20	4.48
101	2006-04-21	4.45
102	2006-04-24	4.42
103	2006-04-25	4.5
104	2006-04-26	4.52
105	2006-04-27	4.5
106	2006-04-28	4.46
107	2006-05-01	4.51
108	2006-05-02	4.48
109	2006-05-03	4.48
110	2006-05-04	4.47
111	2006-05-05	4.45
112	2006-05-08	4.45
113	2006-05-09	4.46
114	2006-05-10	4.47
115	2006-05-11	4.46
116	2006-05-12	4.45
117	2006-05-15	4.41
118	2006-05-16	4.37
119	2006-05-17	4.38
120	2006-05-18	4.31
121	2006-05-19	4.33
122	2006-05-23	4.32
123	2006-05-24	4.3
124	2006-05-25	4.29
125	2006-05-26	4.3
126	2006-05-29	4.3
127	2006-05-30	4.35
128	2006-05-31	4.45
129	2006-06-01	4.39
130	2006-06-02	4.31
131	2006-06-05	4.31
132	2006-06-06	4.29
133	2006-06-07	4.31
134	2006-06-08	4.32
135	2006-06-09	4.37
136	2006-06-12	4.35
137	2006-06-13	4.31
138	2006-06-14	4.35
139	2006-06-15	4.39
140	2006-06-16	4.39
141	2006-06-19	4.41
142	2006-06-20	4.49
143	2006-06-21	4.52
144	2006-06-22	4.56
145	2006-06-23	4.61
146	2006-06-26	4.64
147	2006-06-27	4.6
148	2006-06-28	4.63
149	2006-06-29	4.58
150	2006-06-30	4.58
151	2006-07-04	4.6
152	2006-07-05	4.61
153	2006-07-06	4.59
154	2006-07-07	4.49
155	2006-07-10	4.46
156	2006-07-11	4.44
157	2006-07-12	4.46
158	2006-07-13	4.45
159	2006-07-14	4.44
160	2006-07-17	4.44
161	2006-07-18	4.49
162	2006-07-19	4.43
163	2006-07-20	4.42
164	2006-07-21	4.38
165	2006-07-24	4.37
166	2006-07-25	4.4
167	2006-07-26	4.38
168	2006-07-27	4.38
169	2006-07-28	4.34
170	2006-07-31	4.31
171	2006-08-01	4.31
172	2006-08-02	4.32
173	2006-08-03	4.33
174	2006-08-04	4.3
175	2006-08-08	4.28
176	2006-08-09	4.32
177	2006-08-10	4.33
178	2006-08-11	4.36
179	2006-08-14	4.38
180	2006-08-15	4.32
181	2006-08-16	4.28
182	2006-08-17	4.26
183	2006-08-18	4.22
184	2006-08-21	4.21
185	2006-08-22	4.19
186	2006-08-23	4.2
187	2006-08-24	4.2
188	2006-08-25	4.18
189	2006-08-28	4.18
190	2006-08-29	4.16
191	2006-08-30	4.12
192	2006-08-31	4.11
193	2006-09-01	4.08
194	2006-09-04	4.08
195	2006-09-05	4.14
196	2006-09-06	4.14
197	2006-09-07	4.13
198	2006-09-08	4.1
199	2006-09-11	4.15
200	2006-09-12	4.11
201	2006-09-13	4.12
202	2006-09-14	4.13
203	2006-09-15	4.1
204	2006-09-18	4.12
205	2006-09-19	4.08
206	2006-09-20	4.08
207	2006-09-21	4.02
208	2006-09-22	3.99
209	2006-09-25	3.93
210	2006-09-26	3.97
211	2006-09-27	3.98
212	2006-09-28	3.99
213	2006-09-29	4
214	2006-10-02	3.98
215	2006-10-03	3.99
216	2006-10-04	3.96
217	2006-10-05	4.01
218	2006-10-06	4.08
219	2006-10-10	4.11
220	2006-10-11	4.13
221	2006-10-12	4.14
222	2006-10-13	4.17
223	2006-10-16	4.15
224	2006-10-17	4.17
225	2006-10-18	4.15
226	2006-10-19	4.18
227	2006-10-20	4.18
228	2006-10-23	4.2
229	2006-10-24	4.2
230	2006-10-25	4.17
231	2006-10-26	4.11
232	2006-10-27	4.07
233	2006-10-30	4.07
234	2006-10-31	4.02
235	2006-11-01	3.98
236	2006-11-02	4.02
237	2006-11-03	4.11
238	2006-11-06	4.1
239	2006-11-07	4.05
240	2006-11-08	4.04
241	2006-11-09	4.03
242	2006-11-10	4
243	2006-11-14	3.98
244	2006-11-15	4.02
245	2006-11-16	4.03
246	2006-11-17	4
247	2006-11-20	4
248	2006-11-21	3.99
249	2006-11-22	3.98
250	2006-11-23	3.98
251	2006-11-24	3.97
252	2006-11-27	3.95
253	2006-11-28	3.92
254	2006-11-29	3.94
255	2006-11-30	3.9
Sheet1

Cell Formulas
Range	Formula
B2	B2	=AVERAGEIFS(B4:B5005,A4:A5005,"<="&A1,A4:A5005,">="&EDATE(A1,-12))

 

[mrbeige]

Hey, just wanted to update you to tell you that it worked! Thanks a lot!

 

[kevin9999]

Hey, just wanted to update you to tell you that it worked! Thanks a lot!

Good to hear, and thanks for the feedback