Spreadsheet: 291224d1390226511-calculating-quarterly-averages-from-weekly-data-excel-forum-sample
Sheet: Sheet1

Hi everybody |  |  |  |  |  |  |  |  |  |  | Monthly Quotations
,
I need help generating a formula that takes the weekly values in a quarter and uses them to generate a quarterly average. Quarters are split up as follows.
 |  |  |  |  |  |  |  |  |  |  |  |  | Day | Month | Year | Weekly Values | Monthly
Q#1: 09-11 (First week of September-last week of November) |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Weekly Values | Month | Year |  | Month | Year | Quarterly Values | Weeks
(Used to generate price for January) |  |  |  |  |  |  |  |  |  |  | 2013-01-01 00:00:00 |  | 1 | 1 | 2013 | 911.9335347 |  |  |  | 2013-01-01 00:00:00 | 911.9335347 |  |  |  | 9 | 2013
 |  |  |  |  |  |  |  |  |  |  | 2013-01-08 00:00:00 |  | 8 | 1 | 2013 | 922.4942617 |  |  |  | 2013-01-08 00:00:00 | 922.4942617 |  |  |  | 10 | 2013
Q#2: 12-02 (First week-Last week) |  |  |  |  |  |  |  |  |  |  | 2013-01-15 00:00:00 |  | 15 | 1 | 2013 | 905.2434457 |  |  |  | 2013-01-15 00:00:00 | 905.2434457 |  |  |  | 11 | 2013
 |  |  |  |  |  |  |  |  |  |  | 2013-01-22 00:00:00 |  | 22 | 1 | 2013 | 908.8788563 |  |  |  | 2013-01-22 00:00:00 | 908.8788563 |  |  |  | Average
Q#3: 03-05 (First week-Last week) |  |  |  |  |  |  |  |  |  |  | 2013-01-29 00:00:00 |  | 29 | 1 | 2013 | 897.4777448 |  |  |  | 2013-01-29 00:00:00 | 897.4777448 |  |  |  | 12 | 2013
 |  |  |  |  |  |  |  |  |  |  | 2013-02-05 00:00:00 |  | 5 | 2 | 2013 | 894.5306726 |  |  |  | 2013-02-05 00:00:00 | 894.5306726 |  |  |  | 1 | 2014
Q#4: 06-08 (First week-Last week) |  |  |  |  |  |  |  |  |  |  | 2013-02-12 00:00:00 |  | 12 | 2 | 2013 | 898.0697847 |  |  |  | 2013-02-12 00:00:00 | 898.0697847 |  |  |  | 2 | 2014
 |  |  |  |  |  |  |  |  |  |  | 2013-02-19 00:00:00 |  | 19 | 2 | 2013 | 904.6407186 |  |  |  | 2013-02-19 00:00:00 | 904.6407186 |  |  |  | Average
I've attached an excel sheet with some dummy data. |  |  |  |  |  |  |  |  |  |  | 2013-02-26 00:00:00 |  | 26 | 2 | 2013 | 922.4942617 |  |  |  | 2013-02-26 00:00:00 | 922.4942617 |  |  |  | 3 | 2014
Any suggestions? |  |  |  |  |  |  |  |  |  |  | 2013-03-05 00:00:00 |  | 5 | 3 | 2013 | 936.5310821 |  |  |  | 2013-03-05 00:00:00 | 936.5310821 |  |  |  | 4 | 2014
 |  |  |  |  |  |  |  |  |  |  | 2013-03-12 00:00:00 |  | 12 | 3 | 2013 | 937.1735791 |  |  |  | 2013-03-12 00:00:00 | 937.1735791 |  |  |  | 5 | 2014
 |  |  |  |  |  |  |  |  |  |  | 2013-03-19 00:00:00 |  | 19 | 3 | 2013 | 945.6167572 |  |  |  | 2013-03-19 00:00:00 | 945.6167572 |  |  |  | Average
 |  |  |  |  |  |  |  |  |  |  | 2013-03-26 00:00:00 |  | 26 | 3 | 2013 | 959.2534992 |  |  |  | 2013-03-26 00:00:00 | 959.2534992 |  |  |  | 6 | 2014
 |  |  |  |  |  |  |  |  |  |  | 2013-04-02 00:00:00 |  | 2 | 4 | 2013 | 960.5919003 |  |  |  | 2013-04-02 00:00:00 | 960.5919003 |  |  |  | 7 | 2014
 |  |  |  |  |  |  |  |  |  |  | 2013-04-09 00:00:00 |  | 9 | 4 | 2013 | 946.0949464 |  |  |  | 2013-04-09 00:00:00 | 946.0949464 |  |  |  | 8 | 2014
 |  |  |  |  |  |  |  |  |  |  | 2013-04-16 00:00:00 |  | 16 | 4 | 2013 | 941.5841584 |  |  |  | 2013-04-16 00:00:00 | 941.5841584 |  |  |  | Average
 |  |  |  |  |  |  |  |  |  |  | 2013-04-23 00:00:00 |  | 23 | 4 | 2013 | 948.6943164 |  |  |  | 2013-04-23 00:00:00 | 948.6943164
 |  |  |  |  |  |  |  |  |  |  | 2013-04-30 00:00:00 |  | 30 | 4 | 2013 | 938.3915023 |  |  |  | 2013-04-30 00:00:00 | 938.3915023
 |  |  |  |  |  |  |  |  |  |  | 2013-05-07 00:00:00 |  | 7 | 5 | 2013 | 944.1558442 |  |  |  | 2013-05-07 00:00:00 | 944.1558442
 |  |  |  |  |  |  |  |  |  |  | 2013-05-14 00:00:00 |  | 14 | 5 | 2013 | 951.3097072 |  |  |  | 2013-05-14 00:00:00 | 951.3097072
 |  |  |  |  |  |  |  |  |  |  | 2013-05-21 00:00:00 |  | 21 | 5 | 2013 | 958.5858586 |  |  |  | 2013-05-21 00:00:00 | 958.5858586
 |  |  |  |  |  |  |  |  |  |  | 2013-05-28 00:00:00 |  | 28 | 5 | 2013 | 959.2534992 |  |  |  | 2013-05-28 00:00:00 | 959.2534992
 |  |  |  |  |  |  |  |  |  |  | 2013-06-04 00:00:00 |  | 4 | 6 | 2013 | 941.6220352 |  |  |  | 2013-06-04 00:00:00 | 941.6220352
 |  |  |  |  |  |  |  |  |  |  | 2013-06-11 00:00:00 |  | 11 | 6 | 2013 | 928.313253 |  |  |  | 2013-06-11 00:00:00 | 928.313253
 |  |  |  |  |  |  |  |  |  |  | 2013-06-18 00:00:00 |  | 18 | 6 | 2013 | 920.8955224 |  |  |  | 2013-06-18 00:00:00 | 920.8955224
 |  |  |  |  |  |  |  |  |  |  | 2013-06-25 00:00:00 |  | 25 | 6 | 2013 | 940.9785933 |  |  |  | 2013-06-25 00:00:00 | 940.9785933
 |  |  |  |  |  |  |  |  |  |  | 2013-07-02 00:00:00 |  | 2 | 7 | 2013 | 943.5582822 |  |  |  | 2013-07-02 00:00:00 | 943.5582822
 |  |  |  |  |  |  |  |  |  |  | 2013-07-09 00:00:00 |  | 9 | 7 | 2013 | 960.0469116 |  |  |  | 2013-07-09 00:00:00 | 960.0469116
 |  |  |  |  |  |  |  |  |  |  | 2013-07-16 00:00:00 |  | 16 | 7 | 2013 | 937.1385084 |  |  |  | 2013-07-16 00:00:00 | 937.1385084
 |  |  |  |  |  |  |  |  |  |  | 2013-07-23 00:00:00 |  | 23 | 7 | 2013 | 932.0726172 |  |  |  | 2013-07-23 00:00:00 | 932.0726172
 |  |  |  |  |  |  |  |  |  |  | 2013-07-30 00:00:00 |  | 30 | 7 | 2013 | 930.1886792 |  |  |  | 2013-07-30 00:00:00 | 930.1886792
 |  |  |  |  |  |  |  |  |  |  | 2013-08-06 00:00:00 |  | 6 | 8 | 2013 | 926.4462809917355 |  |  |  | 2013-08-06 00:00:00 | 926.4462809917355
 |  |  |  |  |  |  |  |  |  |  | 2013-08-13 00:00:00 |  | 13 | 8 | 2013 | 930.8157099697885 |  |  |  | 2013-08-13 00:00:00 | 930.8157099697885
 |  |  |  |  |  |  |  |  |  |  | 2013-08-20 00:00:00 |  | 20 | 8 | 2013 | 919.0618019359642 |  |  |  | 2013-08-20 00:00:00 | 919.0618019359642
 |  |  |  |  |  |  |  |  |  |  | 2013-08-27 00:00:00 |  | 27 | 8 | 2013 | 921.5085884988798 |  |  |  | 2013-08-27 00:00:00 | 921.5085884988798
 |  |  |  |  |  |  |  |  |  |  | 2013-09-03 00:00:00 |  | 3 | 9 | 2013 | 935.8662613981762 |  |  |  | 2013-09-03 00:00:00 | 935.8662613981762
 |  |  |  |  |  |  |  |  |  |  | 2013-09-10 00:00:00 |  | 10 | 9 | 2013 | 928.3132530120481 |  |  |  | 2013-09-10 00:00:00 | 928.3132530120481
 |  |  |  |  |  |  |  |  |  |  | 2013-09-17 00:00:00 |  | 17 | 9 | 2013 | 923.9700374531835 |  |  |  | 2013-09-17 00:00:00 | 923.9700374531835
 |  |  |  |  |  |  |  |  |  |  | 2013-09-24 00:00:00 |  | 24 | 9 | 2013 | 915.418828762046 |  |  |  | 2013-09-24 00:00:00 | 915.418828762046
 |  |  |  |  |  |  |  |  |  |  | 2013-10-01 00:00:00 |  | 1 | 10 | 2013 | 913.0081300813008 |  |  |  | 2013-10-01 00:00:00 | 913.0081300813008
 |  |  |  |  |  |  |  |  |  |  | 2013-10-08 00:00:00 |  | 8 | 10 | 2013 | 908.8235294117646 |  |  |  | 2013-10-08 00:00:00 | 908.8235294117646
 |  |  |  |  |  |  |  |  |  |  | 2013-10-15 00:00:00 |  | 15 | 10 | 2013 | 914.8148148148148 |  |  |  | 2013-10-15 00:00:00 | 914.8148148148148
 |  |  |  |  |  |  |  |  |  |  | 2013-10-22 00:00:00 |  | 22 | 10 | 2013 | 898.8380537400145 |  |  |  | 2013-10-22 00:00:00 | 898.8380537400145
 |  |  |  |  |  |  |  |  |  |  | 2013-10-29 00:00:00 |  | 29 | 10 | 2013 | 898.8380537400145 |  |  |  | 2013-10-29 00:00:00 | 898.8380537400145
 |  |  |  |  |  |  |  |  |  |  | 2013-11-05 00:00:00 |  | 5 | 11 | 2013 | 916.0237388724036 |  |  |  | 2013-11-05 00:00:00 | 916.0237388724036
 |  |  |  |  |  |  |  |  |  |  | 2013-11-12 00:00:00 |  | 12 | 11 | 2013 | 918.4523809523808 |  |  |  | 2013-11-12 00:00:00 | 918.4523809523808
 |  |  |  |  |  |  |  |  |  |  | 2013-11-19 00:00:00 |  | 19 | 11 | 2013 | 913.0081300813008 |  |  |  | 2013-11-19 00:00:00 | 913.0081300813008
 |  |  |  |  |  |  |  |  |  |  | 2013-11-26 00:00:00 |  | 26 | 11 | 2013 | 911.2094395280235 |  |  |  | 2013-11-26 00:00:00 | 911.2094395280235
 |  |  |  |  |  |  |  |  |  |  | 2013-12-03 00:00:00 |  | 3 | 12 | 2013 | 909.4186902133922 |  |  |  | 2013-12-03 00:00:00 | 909.4186902133922
 |  |  |  |  |  |  |  |  |  |  | 2013-12-10 00:00:00 |  | 10 | 12 | 2013 | 898.8380537400145 |  |  |  | 2013-12-10 00:00:00 | 898.8380537400145
 |  |  |  |  |  |  |  |  |  |  | 2013-12-17 00:00:00 |  | 17 | 12 | 2013 | 901.1653313911144 |  |  |  | 2013-12-17 00:00:00 | 901.1653313911144
 |  |  |  |  |  |  |  |  |  |  | 2013-12-24 00:00:00 |  | 24 | 12 | 2013 | 904.6817849305047 |  |  |  | 2013-12-24 00:00:00 | 904.6817849305047
 |  |  |  |  |  |  |  |  |  |  | 2013-12-31 00:00:00 |  | 31 | 12 | 2013 | 898.2583454281568 |  |  |  | 2013-12-31 00:00:00 | 898.2583454281568
 |  |  |  |  |  |  |  |  |  |  | 2014-01-07 00:00:00 |  | 7 | 1 | 2014 | 908.2292432035268 |  |  |  | 2014-01-07 00:00:00 | 908.2292432035268
 |  |  |  |  |  |  |  |  |  |  | 2014-01-14 00:00:00 |  | 14 | 1 | 2014 |  |  |  |  | 2014-01-14 00:00:00
 |  |  |  |  |  |  |  |  |  |  | 2014-01-21 00:00:00 |  | 21 | 1 | 2014 |  |  |  |  | 2014-01-21 00:00:00
 |  |  |  |  |  |  |  |  |  |  | 2014-01-28 00:00:00 |  | 28 | 1 | 2014 |  |  |  |  | 2014-01-28 00:00:00
 |  |  |  |  |  |  |  |  |  |  | 2014-02-04 00:00:00 |  | 4 | 2 | 2014 |  |  |  |  | 2014-02-04 00:00:00
 |  |  |  |  |  |  |  |  |  |  | 2014-02-11 00:00:00 |  | 11 | 2 | 2014 |  |  |  |  | 2014-02-11 00:00:00
 |  |  |  |  |  |  |  |  |  |  | 2014-02-18 00:00:00 |  | 18 | 2 | 2014 |  |  |  |  | 2014-02-18 00:00:00
 |  |  |  |  |  |  |  |  |  |  | 2014-02-25 00:00:00 |  | 25 | 2 | 2014 |  |  |  |  | 2014-02-25 00:00:00
 |  |  |  |  |  |  |  |  |  |  | 2014-03-04 00:00:00 |  | 4 | 3 | 2014 |  |  |  |  | 2014-03-04 00:00:00
 |  |  |  |  |  |  |  |  |  |  | 2014-03-11 00:00:00 |  | 11 | 3 | 2014 |  |  |  |  | 2014-03-11 00:00:00
 |  |  |  |  |  |  |  |  |  |  | 2014-03-18 00:00:00 |  | 18 | 3 | 2014 |  |  |  |  | 2014-03-18 00:00:00
 |  |  |  |  |  |  |  |  |  |  | 2014-03-25 00:00:00 |  | 25 | 3 | 2014 |  |  |  |  | 2014-03-25 00:00:00