Spreadsheet: 274063d1382996823-complex-forecasting-formula-needed-forecasting-methods-in-excel
Sheet: Intentions for forecast

Center | IB_Standarized | Jan-09 | Feb-09 | [NAME] | Apr-09 | May-09 | Jun-09 | Jul-09 | Aug-09 | Sep-09 | Oct-09 | Nov-09 | Dec-09 | Jan-10 | Feb-10 | Mar-10 | Apr-10 | May-10 | Jun-10 | Jul-10 | Aug-10 | Sep-10 | Oct-10 | Nov-10 | Dec-10 | Jan-11 | Feb-11 | Mar-11 | Apr-11 | May-11 | Jun-11 | Jul-11 | Aug-11 | Sep-11 | Oct-11 | Nov-11 | Dec-11 | Jan-12 | Feb-12 | Mar-12 | Apr-12 | May-12 | Jun-12 | Jul-12 | Aug-12 | Sep-12 | Oct-12 | Nov-12 | Dec-12 | [NAME] | Feb-13 | Mar-13 | Apr-13 | [NAME] | Jun-13 | Jul-13 | Aug-13 | 2013-09-13 00:00:00 | 2013-10-13 00:00:00 | 2013-11-13 00:00:00 | 2013-12-13 00:00:00 | 2013-01-14 00:00:00 | 2013-02-14 00:00:00 | 2013-03-14 00:00:00 | 2013-04-14 00:00:00 | 2013-05-14 00:00:00 | 2013-06-14 00:00:00 | 2013-07-14 00:00:00 | 2013-08-14 00:00:00
363 | Squash | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 460 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 7 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
363 | Pickle | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 17 | 8 | 0 | 3 | 1 | 2 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 5 | 4 | 15 | 16 | 2 | 3 | 9 | 10 | 13 | 6 | 26 | 3 | 9 | 0 | 0 | 8 | 0 | 1 | 6 | 0
363 | Cards | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 570 | 541 | 346 | 432 | 488 | 784 | 425 | 462 | 422 | 502 | 668 | 448 | 1080 | 434 | 261 | 424 | 907 | 582 | 755 | 667 | 558 | 433 | 483 | 480 | 677 | 565 | 358 | 392 | 576 | 401 | 835 | 567 | 630 | 360 | 367
363 | Notebooks | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 1 | 6 | 3 | 1 | 4 | 3 | 1 | 82 | 7 | 4 | 1 | 1 | 2 | 15 | 1 | 1 | 2 | 18 | 3 | 3 | 1 | 3 | 2 | 1 | 0 | 3 | 5 | 0 | 0 | 0 | 4 | 1 | 1 | 2
363 | Hangers | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 176 | 216 | 125 | 172 | 177 | 298 | 161 | 162 | 161 | 121 | 258 | 141 | 273 | 179 | 138 | 420 | 243 | 192 | 292 | 234 | 244 | 291 | 279 | 219 | 322 | 324 | 174 | 469 | 253 | 231 | 284 | 253 | 221 | 308 | 230
363 | Tablet | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 18 | 11 | 14 | 20 | 22 | 70 | 66 | 31 | 107 | 22 | 43 | 28 | 39 | 54 | 30 | 29 | 76 | 55 | 48 | 12 | 49 | 55 | 40 | 101 | 60 | 76 | 68 | 53 | 126 | 89 | 72 | 39 | 69 | 52 | 82
363 | Pens | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 645 | 743 | 343 | 615 | 700 | 974 | 656 | 482 | 704 | 856 | 699 | 908 | 1029 | 722 | 563 | 880 | 730 | 959 | 745 | 932 | 738 | 808 | 914 | 849 | 941 | 937 | 533 | 1028 | 786 | 675 | 1062 | 705 | 1010 | 1090 | 817
363 | Pencils | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 8 | 22 | 5 | 32 | 14 | 41 | 39 | 27 | 30 | 40 | 84 | 93 | 66 | 33 | 22 | 68 | 62 | 34 | 50 | 131 | 147 | 208 | 140 | 117 | 114 | 145 | 193 | 163 | 134 | 68 | 124 | 168 | 196 | 159 | 95
Example of things I want to do
1. For this example the center was Opened October of 2010 this means that there are 35 months of data that has accumulated.
2. I want to filter out all data for the first month each center opens. So for this case the data would represent 34 full months of data.
3. The logic would follow this format: If (# of months > 2years) Output Average of all previous same months for the current center. (Sept would be average of all previous Sept's)
4. Another example below illustrates another type of solution and logic I want it to follow.
Center | IB_Standarized | Jan-09 | Feb-09 | [NAME] | Apr-09 | May-09 | Jun-09 | Jul-09 | Aug-09 | Sep-09 | Oct-09 | Nov-09 | Dec-09 | Jan-10 | Feb-10 | Mar-10 | Apr-10 | May-10 | Jun-10 | Jul-10 | Aug-10 | Sep-10 | Oct-10 | Nov-10 | Dec-10 | Jan-11 | Feb-11 | Mar-11 | Apr-11 | May-11 | Jun-11 | Jul-11 | Aug-11 | Sep-11 | Oct-11 | Nov-11 | Dec-11 | Jan-12 | Feb-12 | Mar-12 | Apr-12 | May-12 | Jun-12 | Jul-12 | Aug-12 | Sep-12 | Oct-12 | Nov-12 | Dec-12 | [NAME] | Feb-13 | Mar-13 | Apr-13 | [NAME] | Jun-13 | Jul-13 | Aug-13 | 2013-09-13 00:00:00 | 2013-10-13 00:00:00 | 2013-11-13 00:00:00 | 2013-12-13 00:00:00 | 2013-01-14 00:00:00 | 2013-02-14 00:00:00 | 2013-03-14 00:00:00 | 2013-04-14 00:00:00 | 2013-05-14 00:00:00 | 2013-06-14 00:00:00 | 2013-07-14 00:00:00 | 2013-08-14 00:00:00
5616 | Squash | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0
5616 | Pickle | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 5 | 4 | 12
5616 | Cards | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 218 | 322 | 313 | 432
5616 | Notebooks | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 8 | 0 | 1 | 0
5616 | Hangers | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 113 | 163 | 166 | 165
5616 | Tablet | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 92 | 58 | 97 | 60
5616 | Pens | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 321 | 413 | 500 | 591
5616 | Pencils | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 81 | 51 | 143 | 52
5616 | TRANSITION | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 45 | 0 | 0 | 0
Example of things I want to do
1. For this example the center was opened May of 2013 this means that there are 4 months of data that has accumulated.
2. I want to filter out the first month as I did before. (3 months of data now is left.
3. The logic would follow this format: If (# of months < 2years ) Output the average of all centers > 2 years for months being forecasted.) So [NAME] would now be the average of all centers that have over 24 months of data. It would still compare Sept to only Sept.