Hi,
I'm looking for a formula that in the first phase would return an array of aggregated order intake values based on available weekly order intake. The aggregation needs to group 2,3,4,5 or more weeks (definable by the user) into bigger chunks and preferably return the result as an array.
In the second phase I would then use that array formula to calculate the covariance (cov=stdeva/average).
Example: [TABLE="width: 996"]
<TBODY>[TR]
[TD]WK24</SPAN>[/TD]
[TD]WK23</SPAN>[/TD]
[TD]WK22</SPAN>[/TD]
[TD]WK21</SPAN>[/TD]
[TD]WK20</SPAN>[/TD]
[TD]WK19</SPAN>[/TD]
[TD]WK18</SPAN>[/TD]
[TD]WK17</SPAN>[/TD]
[TD]WK16</SPAN>[/TD]
[TD]WK15</SPAN>[/TD]
[TD]WK14</SPAN>[/TD]
[TD]WK13</SPAN>[/TD]
[TD]WK12</SPAN>[/TD]
[TD]WK11</SPAN>[/TD]
[TD]WK10</SPAN>[/TD]
[TD]WK9</SPAN>[/TD]
[TD]WK8</SPAN>[/TD]
[TD]WK7</SPAN>[/TD]
[TD]WK6</SPAN>[/TD]
[TD]WK5</SPAN>[/TD]
[TD]WK4</SPAN>[/TD]
[TD]WK3</SPAN>[/TD]
[TD]WK2</SPAN>[/TD]
[TD]WK1</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]74</SPAN>[/TD]
[TD="align: right"]79</SPAN>[/TD]
[TD="align: right"]68</SPAN>[/TD]
[TD="align: right"]100</SPAN>[/TD]
[TD="align: right"]18</SPAN>[/TD]
[TD="align: right"]69</SPAN>[/TD]
[TD="align: right"]69</SPAN>[/TD]
[TD="align: right"]19</SPAN>[/TD]
[TD="align: right"]53</SPAN>[/TD]
[TD="align: right"]48</SPAN>[/TD]
[TD="align: right"]95</SPAN>[/TD]
[TD="align: right"]41</SPAN>[/TD]
[TD="align: right"]18</SPAN>[/TD]
[TD="align: right"]100</SPAN>[/TD]
[TD="align: right"]11</SPAN>[/TD]
[TD="align: right"]39</SPAN>[/TD]
[TD="align: right"]60</SPAN>[/TD]
[TD="align: right"]80</SPAN>[/TD]
[TD="align: right"]13</SPAN>[/TD]
[TD="align: right"]21</SPAN>[/TD]
[TD="align: right"]45</SPAN>[/TD]
[TD="align: right"]59</SPAN>[/TD]
[TD="align: right"]88</SPAN>[/TD]
[TD="align: right"]12</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=15><COL span=9></COLGROUP>[/TABLE]
Grouping this weekly order intake data in buckets of 4 weeks at a time would return an array of: {321, 175, 237, 168, 174, 204}
This array would then allow me to calculate the COV = stdeva {321, 175, 237, 168, 174, 204} / average {321, 175, 237, 168, 174, 204} = 0.276
Preferably I want to do the entire calculation in one go. My guess is that this would be possible using an arrayformula (combined with a sumproduct for the grouping of the weeks?) ... but I'm stuck.
Please help ;o)
Kind regards,
Koen
I'm looking for a formula that in the first phase would return an array of aggregated order intake values based on available weekly order intake. The aggregation needs to group 2,3,4,5 or more weeks (definable by the user) into bigger chunks and preferably return the result as an array.
In the second phase I would then use that array formula to calculate the covariance (cov=stdeva/average).
Example: [TABLE="width: 996"]
<TBODY>[TR]
[TD]WK24</SPAN>[/TD]
[TD]WK23</SPAN>[/TD]
[TD]WK22</SPAN>[/TD]
[TD]WK21</SPAN>[/TD]
[TD]WK20</SPAN>[/TD]
[TD]WK19</SPAN>[/TD]
[TD]WK18</SPAN>[/TD]
[TD]WK17</SPAN>[/TD]
[TD]WK16</SPAN>[/TD]
[TD]WK15</SPAN>[/TD]
[TD]WK14</SPAN>[/TD]
[TD]WK13</SPAN>[/TD]
[TD]WK12</SPAN>[/TD]
[TD]WK11</SPAN>[/TD]
[TD]WK10</SPAN>[/TD]
[TD]WK9</SPAN>[/TD]
[TD]WK8</SPAN>[/TD]
[TD]WK7</SPAN>[/TD]
[TD]WK6</SPAN>[/TD]
[TD]WK5</SPAN>[/TD]
[TD]WK4</SPAN>[/TD]
[TD]WK3</SPAN>[/TD]
[TD]WK2</SPAN>[/TD]
[TD]WK1</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]74</SPAN>[/TD]
[TD="align: right"]79</SPAN>[/TD]
[TD="align: right"]68</SPAN>[/TD]
[TD="align: right"]100</SPAN>[/TD]
[TD="align: right"]18</SPAN>[/TD]
[TD="align: right"]69</SPAN>[/TD]
[TD="align: right"]69</SPAN>[/TD]
[TD="align: right"]19</SPAN>[/TD]
[TD="align: right"]53</SPAN>[/TD]
[TD="align: right"]48</SPAN>[/TD]
[TD="align: right"]95</SPAN>[/TD]
[TD="align: right"]41</SPAN>[/TD]
[TD="align: right"]18</SPAN>[/TD]
[TD="align: right"]100</SPAN>[/TD]
[TD="align: right"]11</SPAN>[/TD]
[TD="align: right"]39</SPAN>[/TD]
[TD="align: right"]60</SPAN>[/TD]
[TD="align: right"]80</SPAN>[/TD]
[TD="align: right"]13</SPAN>[/TD]
[TD="align: right"]21</SPAN>[/TD]
[TD="align: right"]45</SPAN>[/TD]
[TD="align: right"]59</SPAN>[/TD]
[TD="align: right"]88</SPAN>[/TD]
[TD="align: right"]12</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=15><COL span=9></COLGROUP>[/TABLE]
Grouping this weekly order intake data in buckets of 4 weeks at a time would return an array of: {321, 175, 237, 168, 174, 204}
This array would then allow me to calculate the COV = stdeva {321, 175, 237, 168, 174, 204} / average {321, 175, 237, 168, 174, 204} = 0.276
Preferably I want to do the entire calculation in one go. My guess is that this would be possible using an arrayformula (combined with a sumproduct for the grouping of the weeks?) ... but I'm stuck.
Please help ;o)
Kind regards,
Koen