Get values from specific date ranges

mrbeige

New Member
Joined
Dec 18, 2023
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • test.png
    test.png
    25.3 KB · Views: 12

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Upvote 0
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-014,05
2005-12-024,08
2005-12-054,11
2005-12-064,05
2005-12-074,07
2005-12-084,04
2005-12-094,11
2005-12-124,13
2005-12-134,12
2005-12-144,08
2005-12-154,06
2005-12-164,01
2005-12-194
2005-12-204,02
2005-12-214,03
2005-12-224
2005-12-233,99
2005-12-283,93
2005-12-293,96
2005-12-303,98
2006-01-033,97
2006-01-043,93
2006-01-053,95
2006-01-063,97
2006-01-093,99
2006-01-104,03
2006-01-114,05
2006-01-124,03
2006-01-133,99
2006-01-163,98
2006-01-173,94
2006-01-183,94
2006-01-194
2006-01-204,01
2006-01-234,04
2006-01-244,05
2006-01-254,11
2006-01-264,14
2006-01-274,15
2006-01-304,17
2006-01-314,17
2006-02-014,18
2006-02-024,18
2006-02-034,16
2006-02-064,21
2006-02-074,2
2006-02-084,21
2006-02-094,21
2006-02-104,22
2006-02-134,2
2006-02-144,22
2006-02-154,21
2006-02-164,19
2006-02-174,12
2006-02-204,11
2006-02-214,13
2006-02-224,1
2006-02-234,13
2006-02-244,15
2006-02-274,15
2006-02-284,12
2006-03-014,15
2006-03-024,18
2006-03-034,22
2006-03-064,27
2006-03-074,22
2006-03-084,2
2006-03-094,21
2006-03-104,24
2006-03-134,25
2006-03-144,18
2006-03-154,19
2006-03-164,12
2006-03-174,14
2006-03-204,16
2006-03-214,2
2006-03-224,19
2006-03-234,21
2006-03-244,13
2006-03-274,16
2006-03-284,21
2006-03-294,23
2006-03-304,27
2006-03-314,26
2006-04-034,29
2006-04-044,32
2006-04-054,32
2006-04-064,35
2006-04-074,41
2006-04-104,4
2006-04-114,38
2006-04-124,4
2006-04-134,44
2006-04-174,42
2006-04-184,4
2006-04-194,44
2006-04-204,48
2006-04-214,45
2006-04-244,42
2006-04-254,5
2006-04-264,52
2006-04-274,5
2006-04-284,46
2006-05-014,51
2006-05-024,48
2006-05-034,48
2006-05-044,47
2006-05-054,45
2006-05-084,45
2006-05-094,46
2006-05-104,47
2006-05-114,46
2006-05-124,45
2006-05-154,41
2006-05-164,37
2006-05-174,38
2006-05-184,31
2006-05-194,33
2006-05-234,32
2006-05-244,3
2006-05-254,29
2006-05-264,3
2006-05-294,3
2006-05-304,35
2006-05-314,45
2006-06-014,39
2006-06-024,31
2006-06-054,31
2006-06-064,29
2006-06-074,31
2006-06-084,32
2006-06-094,37
2006-06-124,35
2006-06-134,31
2006-06-144,35
2006-06-154,39
2006-06-164,39
2006-06-194,41
2006-06-204,49
2006-06-214,52
2006-06-224,56
2006-06-234,61
2006-06-264,64
2006-06-274,6
2006-06-284,63
2006-06-294,58
2006-06-304,58
2006-07-044,6
2006-07-054,61
2006-07-064,59
2006-07-074,49
2006-07-104,46
2006-07-114,44
2006-07-124,46
2006-07-134,45
2006-07-144,44
2006-07-174,44
2006-07-184,49
2006-07-194,43
2006-07-204,42
2006-07-214,38
2006-07-244,37
2006-07-254,4
2006-07-264,38
2006-07-274,38
2006-07-284,34
2006-07-314,31
2006-08-014,31
2006-08-024,32
2006-08-034,33
2006-08-044,3
2006-08-084,28
2006-08-094,32
2006-08-104,33
2006-08-114,36
2006-08-144,38
2006-08-154,32
2006-08-164,28
2006-08-174,26
2006-08-184,22
2006-08-214,21
2006-08-224,19
2006-08-234,2
2006-08-244,2
2006-08-254,18
2006-08-284,18
2006-08-294,16
2006-08-304,12
2006-08-314,11
2006-09-014,08
2006-09-044,08
2006-09-054,14
2006-09-064,14
2006-09-074,13
2006-09-084,1
2006-09-114,15
2006-09-124,11
2006-09-134,12
2006-09-144,13
2006-09-154,1
2006-09-184,12
2006-09-194,08
2006-09-204,08
2006-09-214,02
2006-09-223,99
2006-09-253,93
2006-09-263,97
2006-09-273,98
2006-09-283,99
2006-09-294
2006-10-023,98
2006-10-033,99
2006-10-043,96
2006-10-054,01
2006-10-064,08
2006-10-104,11
2006-10-114,13
2006-10-124,14
2006-10-134,17
2006-10-164,15
2006-10-174,17
2006-10-184,15
2006-10-194,18
2006-10-204,18
2006-10-234,2
2006-10-244,2
2006-10-254,17
2006-10-264,11
2006-10-274,07
2006-10-304,07
2006-10-314,02
2006-11-013,98
2006-11-024,02
2006-11-034,11
2006-11-064,1
2006-11-074,05
2006-11-084,04
2006-11-094,03
2006-11-104
2006-11-143,98
2006-11-154,02
2006-11-164,03
2006-11-174
2006-11-204
2006-11-213,99
2006-11-223,98
2006-11-233,98
2006-11-243,97
2006-11-273,95
2006-11-283,92
2006-11-293,94
2006-11-303,9
 
Upvote 0
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...
Excel Formula:
=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
AB
12006-12-01
24.21
3Values I need
42005-12-014.05
52005-12-024.08
62005-12-054.11
72005-12-064.05
82005-12-074.07
92005-12-084.04
102005-12-094.11
112005-12-124.13
122005-12-134.12
132005-12-144.08
142005-12-154.06
152005-12-164.01
162005-12-194
172005-12-204.02
182005-12-214.03
192005-12-224
202005-12-233.99
212005-12-283.93
222005-12-293.96
232005-12-303.98
242006-01-033.97
252006-01-043.93
262006-01-053.95
272006-01-063.97
282006-01-093.99
292006-01-104.03
302006-01-114.05
312006-01-124.03
322006-01-133.99
332006-01-163.98
342006-01-173.94
352006-01-183.94
362006-01-194
372006-01-204.01
382006-01-234.04
392006-01-244.05
402006-01-254.11
412006-01-264.14
422006-01-274.15
432006-01-304.17
442006-01-314.17
452006-02-014.18
462006-02-024.18
472006-02-034.16
482006-02-064.21
492006-02-074.2
502006-02-084.21
512006-02-094.21
522006-02-104.22
532006-02-134.2
542006-02-144.22
552006-02-154.21
562006-02-164.19
572006-02-174.12
582006-02-204.11
592006-02-214.13
602006-02-224.1
612006-02-234.13
622006-02-244.15
632006-02-274.15
642006-02-284.12
652006-03-014.15
662006-03-024.18
672006-03-034.22
682006-03-064.27
692006-03-074.22
702006-03-084.2
712006-03-094.21
722006-03-104.24
732006-03-134.25
742006-03-144.18
752006-03-154.19
762006-03-164.12
772006-03-174.14
782006-03-204.16
792006-03-214.2
802006-03-224.19
812006-03-234.21
822006-03-244.13
832006-03-274.16
842006-03-284.21
852006-03-294.23
862006-03-304.27
872006-03-314.26
882006-04-034.29
892006-04-044.32
902006-04-054.32
912006-04-064.35
922006-04-074.41
932006-04-104.4
942006-04-114.38
952006-04-124.4
962006-04-134.44
972006-04-174.42
982006-04-184.4
992006-04-194.44
1002006-04-204.48
1012006-04-214.45
1022006-04-244.42
1032006-04-254.5
1042006-04-264.52
1052006-04-274.5
1062006-04-284.46
1072006-05-014.51
1082006-05-024.48
1092006-05-034.48
1102006-05-044.47
1112006-05-054.45
1122006-05-084.45
1132006-05-094.46
1142006-05-104.47
1152006-05-114.46
1162006-05-124.45
1172006-05-154.41
1182006-05-164.37
1192006-05-174.38
1202006-05-184.31
1212006-05-194.33
1222006-05-234.32
1232006-05-244.3
1242006-05-254.29
1252006-05-264.3
1262006-05-294.3
1272006-05-304.35
1282006-05-314.45
1292006-06-014.39
1302006-06-024.31
1312006-06-054.31
1322006-06-064.29
1332006-06-074.31
1342006-06-084.32
1352006-06-094.37
1362006-06-124.35
1372006-06-134.31
1382006-06-144.35
1392006-06-154.39
1402006-06-164.39
1412006-06-194.41
1422006-06-204.49
1432006-06-214.52
1442006-06-224.56
1452006-06-234.61
1462006-06-264.64
1472006-06-274.6
1482006-06-284.63
1492006-06-294.58
1502006-06-304.58
1512006-07-044.6
1522006-07-054.61
1532006-07-064.59
1542006-07-074.49
1552006-07-104.46
1562006-07-114.44
1572006-07-124.46
1582006-07-134.45
1592006-07-144.44
1602006-07-174.44
1612006-07-184.49
1622006-07-194.43
1632006-07-204.42
1642006-07-214.38
1652006-07-244.37
1662006-07-254.4
1672006-07-264.38
1682006-07-274.38
1692006-07-284.34
1702006-07-314.31
1712006-08-014.31
1722006-08-024.32
1732006-08-034.33
1742006-08-044.3
1752006-08-084.28
1762006-08-094.32
1772006-08-104.33
1782006-08-114.36
1792006-08-144.38
1802006-08-154.32
1812006-08-164.28
1822006-08-174.26
1832006-08-184.22
1842006-08-214.21
1852006-08-224.19
1862006-08-234.2
1872006-08-244.2
1882006-08-254.18
1892006-08-284.18
1902006-08-294.16
1912006-08-304.12
1922006-08-314.11
1932006-09-014.08
1942006-09-044.08
1952006-09-054.14
1962006-09-064.14
1972006-09-074.13
1982006-09-084.1
1992006-09-114.15
2002006-09-124.11
2012006-09-134.12
2022006-09-144.13
2032006-09-154.1
2042006-09-184.12
2052006-09-194.08
2062006-09-204.08
2072006-09-214.02
2082006-09-223.99
2092006-09-253.93
2102006-09-263.97
2112006-09-273.98
2122006-09-283.99
2132006-09-294
2142006-10-023.98
2152006-10-033.99
2162006-10-043.96
2172006-10-054.01
2182006-10-064.08
2192006-10-104.11
2202006-10-114.13
2212006-10-124.14
2222006-10-134.17
2232006-10-164.15
2242006-10-174.17
2252006-10-184.15
2262006-10-194.18
2272006-10-204.18
2282006-10-234.2
2292006-10-244.2
2302006-10-254.17
2312006-10-264.11
2322006-10-274.07
2332006-10-304.07
2342006-10-314.02
2352006-11-013.98
2362006-11-024.02
2372006-11-034.11
2382006-11-064.1
2392006-11-074.05
2402006-11-084.04
2412006-11-094.03
2422006-11-104
2432006-11-143.98
2442006-11-154.02
2452006-11-164.03
2462006-11-174
2472006-11-204
2482006-11-213.99
2492006-11-223.98
2502006-11-233.98
2512006-11-243.97
2522006-11-273.95
2532006-11-283.92
2542006-11-293.94
2552006-11-303.9
Sheet1
Cell Formulas
RangeFormula
B2B2=AVERAGEIFS(B4:B5005,A4:A5005,"<="&A1,A4:A5005,">="&EDATE(A1,-12))
 
Upvote 1
Solution

Forum statistics

Threads
1,224,541
Messages
6,179,418
Members
452,912
Latest member
alicemil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top