VBA Loop for each to build forecast

[natekris8183]

I have a data set that includes Column1 (Dates, week-to-week starting on Mondays), Column2 (product by SKU), Columns3 (product status, active... this will eventually be left out as soon as I finish the VBA to permit a list to be built allowing only active products to be selected in the data validation drop down), Column4 (Source: legacy or dealer). The SKUs are unique to the products obviously. I need to create a list range of data set to input into the Worksheet Function Trend based upon dates prior to the current date in the table range (RawSalesDataTbl[@Week]). This will provide a running trend to juxtapose against our proposed forecast. The complication is the data set is not linear thus I cannot simple name a range of "y" values and "x" values. The easy one is naming the proposed "X" value as it will be the next week value, again determined at RawSalesDataTbl[@Week].

 

[michaelsmith559]

Your actual data is pretty non-linear. Here is one of a few ways that I would try. First, I don't think you can use the date per se as a predictor. Second if you sum the acutal values and then take the ln of those values it will linearize the data somewhat. Also, do a chart comparing column a sheet 2 to column j sheet 2. I'll post the sheets, and macros. Hopefully it is self-explanatory. Sheet 1 contains the ln data and prediction intervals. Sheet 2 will contain the actual forecast (column J). Also, I have not carried down the forecast past row 99 as I can only post 100 rows. Hope you get the idea of what to do to your data. Also, not sure if I can post the chart here or if I can how to do it.

Sheet 1:

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Week	Actual	Forecast					Small Table of z-values for Confidence Intervals				Lower PI	Upper PI
2	1	2.302585	2.302585		SMOOTHING	DAMPING	TOTAL					1.30	3.30
3	2	2.890372	2.302585		0.900000095	0.0999999	1	Confidence Level	a	1-(a/2)	z	1.30	3.30
4	3	3.218876	2.831593					5%	0.95	0.525	0.062707	1.83	3.83
5	4	3.465736	3.180148		Prediction Interval	ERROR	Row Check	10%	0.90	0.55	0.125661	2.18	4.18
6	5	3.610918	3.437177		99%	0.1500487		15%	0.85	0.575	0.189118	2.44	4.43
7	6	3.637586	3.593544		0.997776352			20%	0.80	0.6	0.253347	2.60	4.59
8	7	3.713572	3.633182					25%	0.75	0.625	0.318639	2.64	4.63
9	8	3.73767	3.705533					30%	0.70	0.65	0.38532	2.71	4.70
10	9	3.951244	3.734456					35%	0.65	0.675	0.453762	2.74	4.73
11	10	4.007333	3.929565		SMOOTHING	ERROR		40%	0.60	0.7	0.524401	2.93	4.93
12	11	4.110874	3.999556		0.100000001	0.9321231		45%	0.55	0.725	0.59776	3.00	5.00
13	12	4.127134	4.099742		0.200000003	0.5113303		50%	0.50	0.75	0.67449	3.10	5.10
14	13	4.174387	4.124395		0.300000012	0.3605713		55%	0.45	0.775	0.755415	3.13	5.12
15	14	4.454347	4.169388		0.400000006	0.2826514		60%	0.40	0.8	0.841621	3.17	5.17
16	15	4.574711	4.425851		0.5	0.2349407		65%	0.35	0.825	0.934589	3.43	5.42
17	16	4.624973	4.559825		0.600000024	0.2028293		70%	0.30	0.85	1.036433	3.56	5.56
18	17	4.672829	4.618458		0.700000048	0.179918		75%	0.25	0.875	1.150349	3.62	5.62
19	18	4.762174	4.667392		0.800000072	0.1629393		80%	0.20	0.9	1.281552	3.67	5.67
20	19	5.117994	4.752696		0.900000095	0.1500487		85%	0.15	0.925	1.439531	3.75	5.75
21	20	5.411646	5.081464					90%	0.10	0.95	1.644854	4.08	6.08
22	21	5.55296	5.378628					92%	0.08	0.96	1.750686	4.38	6.38
23	22	5.726848	5.535526					95%	0.05	0.975	1.959964	4.54	6.53
24	23	6.369901	5.707716					96%	0.04	0.98	2.053749	4.71	6.71
25	24	6.628041	6.303683					98%	0.02	0.99	2.326348	5.31	7.30
26	25	6.830874	6.595606					99%	0.01	0.995	2.575829	5.60	7.59
27	26	7.003974	6.807347					99.9%	0.001	0.9995	3.290527	5.81	7.81
28	27	7.390799	6.984311					99.99%	0.0001	0.99995	3.890592	5.99	7.98
29	28	7.392648	7.35015									6.35	8.35
30	29	7.427739	7.388398									6.39	8.39
31	30	7.466228	7.423805									6.43	8.42
32	31	7.488853	7.461985									6.46	8.46
33	32	7.520235	7.486166									6.49	8.48
34	33	7.68156	7.516828									6.52	8.51
35	34	7.769801	7.665087									6.67	8.66
36	35	7.850104	7.75933									6.76	8.76
37	36	7.926603	7.841026									6.84	8.84
38	37	8.124743	7.918045									6.92	8.92
39	38	8.125927	8.104073									7.11	9.10
40	39	8.142936	8.123741									7.13	9.12
41	40	8.161946	8.141017									7.14	9.14
42	41	8.173293	8.159853									7.16	9.16
43	42	8.189245	8.171949									7.17	9.17
44	43	8.275122	8.187515									7.19	9.19
45	44	8.324821	8.266361									7.27	9.26
46	45	8.371705	8.318975									7.32	9.32
47	46	8.417815	8.366432									7.37	9.36
48	47	8.54364	8.412676									7.41	9.41
49	48	8.544419	8.530544									7.53	9.53
50	49	8.548692	8.543032									7.55	9.54
51	50	8.609772	8.548126									7.55	9.55
52	51	8.696176	8.603608									7.61	9.60
53	52	8.769973	8.686919									7.69	9.68
54	53	8.847216	8.761668									7.76	9.76
55	54	8.964823	8.838661									7.84	9.84
56	55	9.053804	8.952207									7.95	9.95
57	56	9.132595	9.043644									8.05	10.04
58	57	9.21144	9.1237									8.13	10.12
59	58	9.400051	9.202666									8.20	10.20
60	59	9.401126	9.380313									8.38	10.38
61	60	9.401291	9.399045									8.40	10.40
62	61	9.415971	9.401067									8.40	10.40
63	62	9.433164	9.414481									8.42	10.41
64	63	9.44288	9.431296									8.43	10.43
65	64	9.456497	9.441721									8.44	10.44
66	65	9.532931	9.455019									8.46	10.45
67	66	9.576787	9.52514									8.53	10.52
68	67	9.621125	9.571623									8.57	10.57
69	68	9.662435	9.616174									8.62	10.61
70	69	9.770413	9.657809									8.66	10.66
71	70	9.771098	9.759153									8.76	10.76
72	71	9.772695	9.769904									8.77	10.77
73	72	9.775825	9.772416									8.77	10.77
74	73	9.782506	9.775484									8.78	10.77
75	74	9.785548	9.781803									8.78	10.78
76	75	9.793003	9.785174									8.79	10.78
77	76	9.796069	9.79222									8.79	10.79
78	77	9.802728	9.795684									8.80	10.79
79	78	9.805213	9.802023									8.80	10.80
80	79	9.830379	9.804894									8.81	10.80
81	80	9.831132	9.827831									8.83	10.83
82	81	9.839536	9.830802									8.83	10.83
83	82	9.846706	9.838662									8.84	10.84
84	83	9.854402	9.845901									8.85	10.84
85	84	9.861258	9.853552									8.86	10.85
86	85	9.889338	9.860488									8.86	10.86
87	86	9.925445	9.886453									8.89	10.88
88	87	9.938613	9.921546									8.92	10.92
89	88	9.9732	9.936907									8.94	10.93
90	89	10.06586	9.969571									8.97	10.97
91	90	10.06684	10.05623									9.06	11.05
92	91	10.06726	10.06578									9.07	11.06
93	92	10.07002	10.06711									9.07	11.06
94	93	10.07471	10.06973									9.07	11.07
95	94	10.07605	10.07421									9.08	11.07
96	95	10.07937	10.07587									9.08	11.07
97	96	10.10267	10.07902									9.08	11.08
98	97	10.10671	10.1003									9.10	11.10
99	98	10.10741	10.10607									9.11	11.10
Sheet1

Cell Formulas
Range	Formula
L2	=C2-$E$7
L3	=C3-$E$7
L4	=C4-$E$7
L5	=C5-$E$7
L6	=C6-$E$7
L7	=C7-$E$7
L8	=C8-$E$7
L9	=C9-$E$7
L10	=C10-$E$7
L11	=C11-$E$7
L12	=C12-$E$7
L13	=C13-$E$7
L14	=C14-$E$7
L15	=C15-$E$7
L16	=C16-$E$7
L17	=C17-$E$7
L18	=C18-$E$7
L19	=C19-$E$7
L20	=C20-$E$7
L21	=C21-$E$7
L22	=C22-$E$7
L23	=C23-$E$7
L24	=C24-$E$7
L25	=C25-$E$7
L26	=C26-$E$7
L27	=C27-$E$7
L28	=C28-$E$7
L29	=C29-$E$7
L30	=C30-$E$7
L31	=C31-$E$7
L32	=C32-$E$7
L33	=C33-$E$7
L34	=C34-$E$7
L35	=C35-$E$7
L36	=C36-$E$7
L37	=C37-$E$7
L38	=C38-$E$7
L39	=C39-$E$7
L40	=C40-$E$7
L41	=C41-$E$7
L42	=C42-$E$7
L43	=C43-$E$7
L44	=C44-$E$7
L45	=C45-$E$7
L46	=C46-$E$7
L47	=C47-$E$7
L48	=C48-$E$7
L49	=C49-$E$7
L50	=C50-$E$7
L51	=C51-$E$7
L52	=C52-$E$7
L53	=C53-$E$7
L54	=C54-$E$7
L55	=C55-$E$7
L56	=C56-$E$7
L57	=C57-$E$7
L58	=C58-$E$7
L59	=C59-$E$7
L60	=C60-$E$7
L61	=C61-$E$7
L62	=C62-$E$7
L63	=C63-$E$7
L64	=C64-$E$7
L65	=C65-$E$7
L66	=C66-$E$7
L67	=C67-$E$7
L68	=C68-$E$7
L69	=C69-$E$7
L70	=C70-$E$7
L71	=C71-$E$7
L72	=C72-$E$7
L73	=C73-$E$7
L74	=C74-$E$7
L75	=C75-$E$7
L76	=C76-$E$7
L77	=C77-$E$7
L78	=C78-$E$7
L79	=C79-$E$7
L80	=C80-$E$7
L81	=C81-$E$7
L82	=C82-$E$7
L83	=C83-$E$7
L84	=C84-$E$7
L85	=C85-$E$7
L86	=C86-$E$7
L87	=C87-$E$7
L88	=C88-$E$7
L89	=C89-$E$7
L90	=C90-$E$7
L91	=C91-$E$7
L92	=C92-$E$7
L93	=C93-$E$7
L94	=C94-$E$7
L95	=C95-$E$7
L96	=C96-$E$7
L97	=C97-$E$7
L98	=C98-$E$7
L99	=C99-$E$7
M2	=C2+$E$7
M3	=C3+$E$7
M4	=C4+$E$7
M5	=C5+$E$7
M6	=C6+$E$7
M7	=C7+$E$7
M8	=C8+$E$7
M9	=C9+$E$7
M10	=C10+$E$7
M11	=C11+$E$7
M12	=C12+$E$7
M13	=C13+$E$7
M14	=C14+$E$7
M15	=C15+$E$7
M16	=C16+$E$7
M17	=C17+$E$7
M18	=C18+$E$7
M19	=C19+$E$7
M20	=C20+$E$7
M21	=C21+$E$7
M22	=C22+$E$7
M23	=C23+$E$7
M24	=C24+$E$7
M25	=C25+$E$7
M26	=C26+$E$7
M27	=C27+$E$7
M28	=C28+$E$7
M29	=C29+$E$7
M30	=C30+$E$7
M31	=C31+$E$7
M32	=C32+$E$7
M33	=C33+$E$7
M34	=C34+$E$7
M35	=C35+$E$7
M36	=C36+$E$7
M37	=C37+$E$7
M38	=C38+$E$7
M39	=C39+$E$7
M40	=C40+$E$7
M41	=C41+$E$7
M42	=C42+$E$7
M43	=C43+$E$7
M44	=C44+$E$7
M45	=C45+$E$7
M46	=C46+$E$7
M47	=C47+$E$7
M48	=C48+$E$7
M49	=C49+$E$7
M50	=C50+$E$7
M51	=C51+$E$7
M52	=C52+$E$7
M53	=C53+$E$7
M54	=C54+$E$7
M55	=C55+$E$7
M56	=C56+$E$7
M57	=C57+$E$7
M58	=C58+$E$7
M59	=C59+$E$7
M60	=C60+$E$7
M61	=C61+$E$7
M62	=C62+$E$7
M63	=C63+$E$7
M64	=C64+$E$7
M65	=C65+$E$7
M66	=C66+$E$7
M67	=C67+$E$7
M68	=C68+$E$7
M69	=C69+$E$7
M70	=C70+$E$7
M71	=C71+$E$7
M72	=C72+$E$7
M73	=C73+$E$7
M74	=C74+$E$7
M75	=C75+$E$7
M76	=C76+$E$7
M77	=C77+$E$7
M78	=C78+$E$7
M79	=C79+$E$7
M80	=C80+$E$7
M81	=C81+$E$7
M82	=C82+$E$7
M83	=C83+$E$7
M84	=C84+$E$7
M85	=C85+$E$7
M86	=C86+$E$7
M87	=C87+$E$7
M88	=C88+$E$7
M89	=C89+$E$7
M90	=C90+$E$7
M91	=C91+$E$7
M92	=C92+$E$7
M93	=C93+$E$7
M94	=C94+$E$7
M95	=C95+$E$7
M96	=C96+$E$7
M97	=C97+$E$7
M98	=C98+$E$7
M99	=C99+$E$7
F3	=1-E3
G3	=SUM(E3:F3)
I4	=1-H4
I5	=1-H5
I6	=1-H6
I7	=1-H7
I8	=1-H8
I9	=1-H9
I10	=1-H10
I11	=1-H11
I12	=1-H12
I13	=1-H13
I14	=1-H14
I15	=1-H15
I16	=1-H16
I17	=1-H17
I18	=1-H18
I19	=1-H19
I20	=1-H20
I21	=1-H21
I22	=1-H22
I23	=1-H23
I24	=1-H24
I25	=1-H25
I26	=1-H26
I27	=1-H27
I28	=1-H28
J4	=1-(I4/2)
J5	=1-(I5/2)
J6	=1-(I6/2)
J7	=1-(I7/2)
J8	=1-(I8/2)
J9	=1-(I9/2)
J10	=1-(I10/2)
J11	=1-(I11/2)
J12	=1-(I12/2)
J13	=1-(I13/2)
J14	=1-(I14/2)
J15	=1-(I15/2)
J16	=1-(I16/2)
J17	=1-(I17/2)
J18	=1-(I18/2)
J19	=1-(I19/2)
J20	=1-(I20/2)
J21	=1-(I21/2)
J22	=1-(I22/2)
J23	=1-(I23/2)
J24	=1-(I24/2)
J25	=1-(I25/2)
J26	=1-(I26/2)
J27	=1-(I27/2)
J28	=1-(I28/2)
K4	=NORMSINV(J4)
K5	=NORMSINV(J5)
K6	=NORMSINV(J6)
K7	=NORMSINV(J7)
K8	=NORMSINV(J8)
K9	=NORMSINV(J9)
K10	=NORMSINV(J10)
K11	=NORMSINV(J11)
K12	=NORMSINV(J12)
K13	=NORMSINV(J13)
K14	=NORMSINV(J14)
K15	=NORMSINV(J15)
K16	=NORMSINV(J16)
K17	=NORMSINV(J17)
K18	=NORMSINV(J18)
K19	=NORMSINV(J19)
K20	=NORMSINV(J20)
K21	=NORMSINV(J21)
K22	=NORMSINV(J22)
K23	=NORMSINV(J23)
K24	=NORMSINV(J24)
K25	=NORMSINV(J25)
K26	=NORMSINV(J26)
K27	=NORMSINV(J27)
K28	=NORMSINV(J28)
E7	=VLOOKUP($E$6,$H$4:$K$28,4,FALSE)*SQRT(F6)
C2	=B2
C3	=B2
C4	=$E$3*B3+$F$3*C3
C5	=$E$3*B4+$F$3*C4
C6	=$E$3*B5+$F$3*C5
C7	=$E$3*B6+$F$3*C6
C8	=$E$3*B7+$F$3*C7
C9	=$E$3*B8+$F$3*C8
C10	=$E$3*B9+$F$3*C9
C11	=$E$3*B10+$F$3*C10
C12	=$E$3*B11+$F$3*C11
C13	=$E$3*B12+$F$3*C12
C14	=$E$3*B13+$F$3*C13
C15	=$E$3*B14+$F$3*C14
C16	=$E$3*B15+$F$3*C15
C17	=$E$3*B16+$F$3*C16
C18	=$E$3*B17+$F$3*C17
C19	=$E$3*B18+$F$3*C18
C20	=$E$3*B19+$F$3*C19
C21	=$E$3*B20+$F$3*C20
C22	=$E$3*B21+$F$3*C21
C23	=$E$3*B22+$F$3*C22
C24	=$E$3*B23+$F$3*C23
C25	=$E$3*B24+$F$3*C24
C26	=$E$3*B25+$F$3*C25
C27	=$E$3*B26+$F$3*C26
C28	=$E$3*B27+$F$3*C27
C29	=$E$3*B28+$F$3*C28
C30	=$E$3*B29+$F$3*C29
C31	=$E$3*B30+$F$3*C30
C32	=$E$3*B31+$F$3*C31
C33	=$E$3*B32+$F$3*C32
C34	=$E$3*B33+$F$3*C33
C35	=$E$3*B34+$F$3*C34
C36	=$E$3*B35+$F$3*C35
C37	=$E$3*B36+$F$3*C36
C38	=$E$3*B37+$F$3*C37
C39	=$E$3*B38+$F$3*C38
C40	=$E$3*B39+$F$3*C39
C41	=$E$3*B40+$F$3*C40
C42	=$E$3*B41+$F$3*C41
C43	=$E$3*B42+$F$3*C42
C44	=$E$3*B43+$F$3*C43
C45	=$E$3*B44+$F$3*C44
C46	=$E$3*B45+$F$3*C45
C47	=$E$3*B46+$F$3*C46
C48	=$E$3*B47+$F$3*C47
C49	=$E$3*B48+$F$3*C48
C50	=$E$3*B49+$F$3*C49
C51	=$E$3*B50+$F$3*C50
C52	=$E$3*B51+$F$3*C51
C53	=$E$3*B52+$F$3*C52
C54	=$E$3*B53+$F$3*C53
C55	=$E$3*B54+$F$3*C54
C56	=$E$3*B55+$F$3*C55
C57	=$E$3*B56+$F$3*C56
C58	=$E$3*B57+$F$3*C57
C59	=$E$3*B58+$F$3*C58
C60	=$E$3*B59+$F$3*C59
C61	=$E$3*B60+$F$3*C60
C62	=$E$3*B61+$F$3*C61
C63	=$E$3*B62+$F$3*C62
C64	=$E$3*B63+$F$3*C63
C65	=$E$3*B64+$F$3*C64
C66	=$E$3*B65+$F$3*C65
C67	=$E$3*B66+$F$3*C66
C68	=$E$3*B67+$F$3*C67
C69	=$E$3*B68+$F$3*C68
C70	=$E$3*B69+$F$3*C69
C71	=$E$3*B70+$F$3*C70
C72	=$E$3*B71+$F$3*C71
C73	=$E$3*B72+$F$3*C72
C74	=$E$3*B73+$F$3*C73
C75	=$E$3*B74+$F$3*C74
C76	=$E$3*B75+$F$3*C75
C77	=$E$3*B76+$F$3*C76
C78	=$E$3*B77+$F$3*C77
C79	=$E$3*B78+$F$3*C78
C80	=$E$3*B79+$F$3*C79
C81	=$E$3*B80+$F$3*C80
C82	=$E$3*B81+$F$3*C81
C83	=$E$3*B82+$F$3*C82
C84	=$E$3*B83+$F$3*C83
C85	=$E$3*B84+$F$3*C84
C86	=$E$3*B85+$F$3*C85
C87	=$E$3*B86+$F$3*C86
C88	=$E$3*B87+$F$3*C87
C89	=$E$3*B88+$F$3*C88
C90	=$E$3*B89+$F$3*C89
C91	=$E$3*B90+$F$3*C90
C92	=$E$3*B91+$F$3*C91
C93	=$E$3*B92+$F$3*C92
C94	=$E$3*B93+$F$3*C93
C95	=$E$3*B94+$F$3*C94
C96	=$E$3*B95+$F$3*C95
C97	=$E$3*B96+$F$3*C96
C98	=$E$3*B97+$F$3*C97
C99	=$E$3*B98+$F$3*C98

Sheet 2:

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K
1	Actual	Sum	LN		Lower PI	Forecast	Upper PI		Actual Lower PI	Actual Forecast	Actual Upper PI
2	10	10	2.302585		4	10	27		-6	0	17
3	8	18	2.890372		4	10	27		-14	8	9
4	7	25	3.218876		6	17	46		-19	8	21
5	7	32	3.465736		9	24	65		-23	8	33
6	5	37	3.610918		11	31	84		-26	6	47
7	1	38	3.637586		13	36	99		-25	2	61
8	3	41	3.713572		14	38	103		-27	3	62
9	1	42	3.73767		15	41	110		-27	1	68
10	10	52	3.951244		15	42	114		-37	10	62
11	3	55	4.007333		19	51	138		-36	4	83
12	6	61	4.110874		20	55	148		-41	6	87
13	1	62	4.127134		22	60	164		-40	2	102
14	3	65	4.174387		23	62	168		-42	3	103
15	21	86	4.454347		24	65	175		-62	21	89
16	11	97	4.574711		31	84	227		-66	13	130
17	5	102	4.624973		35	96	259		-67	6	157
18	5	107	4.672829		37	101	275		-70	6	168
19	10	117	4.762174		39	106	289		-78	11	172
20	50	167	5.117994		43	116	314		-124	51	147
21	57	224	5.411646		59	161	437		-165	63	213
22	34	258	5.55296		80	217	588		-178	41	330
23	49	307	5.726848		93	254	688		-214	53	381
24	277	584	6.369901		111	301	817		-473	283	233
25	172	756	6.628041		202	547	1482		-554	209	726
26	170	926	6.830874		270	732	1985		-656	194	1059
27	175	1101	7.003974		333	904	2453		-768	197	1352
28	520	1621	7.390799		398	1080	2928		-1223	541	1307
29	3	1624	7.392648		574	1556	4221		-1050	68	2597
30	58	1682	7.427739		596	1617	4386		-1086	65	2704
31	66	1748	7.466228		618	1675	4544		-1130	73	2796
32	40	1788	7.488853		642	1741	4721		-1146	47	2933
33	57	1845	7.520235		657	1783	4836		-1188	62	2991
34	323	2168	7.68156		678	1839	4987		-1490	329	2819
35	200	2368	7.769801		786	2133	5784		-1582	235	3416
36	198	2566	7.850104		864	2343	6356		-1702	223	3790
37	204	2770	7.926603		938	2543	6897		-1832	227	4127
38	607	3377	8.124743		1013	2746	7449		-2364	631	4072
39	4	3381	8.125927		1220	3308	8972		-2161	73	5591
40	58	3439	8.142936		1244	3374	9150		-2195	65	5711
41	66	3505	8.161946		1266	3432	9310		-2239	73	5805
42	40	3545	8.173293		1290	3498	9487		-2255	47	5942
43	57	3602	8.189245		1305	3540	9602		-2297	62	6000
44	323	3925	8.275122		1326	3596	9753		-2599	329	5828
45	200	4125	8.324821		1435	3891	10553		-2690	234	6428
46	198	4323	8.371705		1512	4101	11123		-2811	222	6800
47	204	4527	8.417815		1586	4300	11663		-2941	227	7136
48	607	5134	8.54364		1661	4504	12215		-3473	630	7081
49	4	5138	8.544419		1868	5067	13743		-3270	71	8605
50	22	5160	8.548692		1892	5131	13916		-3268	29	8756
51	325	5485	8.609772		1901	5157	13987		-3584	328	8502
52	495	5980	8.696176		2010	5451	14785		-3970	529	8805
53	458	6438	8.769973		2185	5925	16070		-4253	513	9632
54	517	6955	8.847216		2354	6385	17317		-4601	570	10362
55	868	7823	8.964823		2542	6896	18703		-5281	927	10880
56	728	8551	9.053804		2848	7725	20952		-5703	826	12401
57	701	9252	9.132595		3121	8465	22958		-6131	787	13706
58	759	10011	9.21144		3381	9170	24871		-6630	841	14860
59	2078	12089	9.400051		3659	9924	26915		-8430	2165	14826
60	13	12102	9.401126		4370	11853	32147		-7732	249	20045
61	2	12104	9.401291		4453	12077	32755		-7651	27	20651
62	179	12283	9.415971		4462	12101	32822		-7821	182	20539
63	213	12496	9.433164		4522	12265	33265		-7974	231	20769
64	122	12618	9.44288		4599	12473	33829		-8019	145	21211
65	173	12791	9.456497		4647	12603	34183		-8144	188	21392
66	1016	13807	9.532931		4709	12772	34641		-9098	1035	20834
67	619	14426	9.576787		5051	13700	37157		-9375	726	22731
68	654	15080	9.621125		5291	14352	38925		-9789	728	23845
69	636	15716	9.662435		5533	15006	40699		-10183	710	24983
70	1792	17508	9.770413		5768	15643	42429		-11740	1865	24921
71	12	17520	9.771098		6383	17312	46954		-11137	208	29434
72	28	17548	9.772695		6452	17499	47462		-11096	49	29914
73	55	17603	9.775825		6468	17543	47581		-11135	60	29978
74	118	17721	9.782506		6488	17597	47727		-11233	124	30006
75	54	17775	9.785548		6529	17709	48030		-11246	66	30255
76	133	17908	9.793003		6551	17768	48192		-11357	140	30284
77	55	17963	9.796069		6597	17894	48533		-11366	69	30570
78	120	18083	9.802728		6620	17956	48701		-11463	127	30618
79	45	18128	9.805213		6662	18070	49011		-11466	58	30883
80	462	18590	9.830379		6682	18122	49152		-11908	468	30562
81	14	18604	9.831132		6837	18543	50292		-11767	61	31688
82	157	18761	9.839536		6857	18598	50442		-11904	163	31681
83	135	18896	9.846706		6911	18745	50840		-11985	151	31944
84	146	19042	9.854402		6961	18881	51209		-12081	161	32167
85	131	19173	9.861258		7015	19026	51603		-12158	147	32430
86	546	19719	9.889338		7064	19158	51962		-12655	561	32243
87	725	20444	9.925445		7249	19662	53329		-13195	782	32885
88	271	20715	9.938613		7508	20364	55233		-13207	351	34518
89	729	21444	9.9732		7625	20680	56088		-13819	764	34644
90	2082	23526	10.06586		7878	21366	57951		-15648	2160	34425
91	23	23549	10.06684		8591	23301	63197		-14958	248	39648
92	10	23559	10.06726		8673	23524	63803		-14886	35	40244
93	65	23624	10.07002		8685	23556	63888		-14939	68	40264
94	111	23735	10.07471		8708	23617	64055		-15027	118	40320
95	32	23767	10.07605		8747	23723	64343		-15020	44	40576
96	79	23846	10.07937		8761	23763	64450		-15085	83	40604
97	562	24408	10.10267		8789	23838	64654		-15619	570	40246
98	99	24507	10.10671		8978	24350	66044		-15529	157	41537
99	17	24524	10.10741		9030	24491	66426		-15494	33	41902
Sheet2

Cell Formulas
Range	Formula
B2	=SUM($A$2:A2)
B3	=SUM($A$2:A3)
B4	=SUM($A$2:A4)
B5	=SUM($A$2:A5)
B6	=SUM($A$2:A6)
B7	=SUM($A$2:A7)
B8	=SUM($A$2:A8)
B9	=SUM($A$2:A9)
B10	=SUM($A$2:A10)
B11	=SUM($A$2:A11)
B12	=SUM($A$2:A12)
B13	=SUM($A$2:A13)
B14	=SUM($A$2:A14)
B15	=SUM($A$2:A15)
B16	=SUM($A$2:A16)
B17	=SUM($A$2:A17)
B18	=SUM($A$2:A18)
B19	=SUM($A$2:A19)
B20	=SUM($A$2:A20)
B21	=SUM($A$2:A21)
B22	=SUM($A$2:A22)
B23	=SUM($A$2:A23)
B24	=SUM($A$2:A24)
B25	=SUM($A$2:A25)
B26	=SUM($A$2:A26)
B27	=SUM($A$2:A27)
B28	=SUM($A$2:A28)
B29	=SUM($A$2:A29)
B30	=SUM($A$2:A30)
B31	=SUM($A$2:A31)
B32	=SUM($A$2:A32)
B33	=SUM($A$2:A33)
B34	=SUM($A$2:A34)
B35	=SUM($A$2:A35)
B36	=SUM($A$2:A36)
B37	=SUM($A$2:A37)
B38	=SUM($A$2:A38)
B39	=SUM($A$2:A39)
B40	=SUM($A$2:A40)
B41	=SUM($A$2:A41)
B42	=SUM($A$2:A42)
B43	=SUM($A$2:A43)
B44	=SUM($A$2:A44)
B45	=SUM($A$2:A45)
B46	=SUM($A$2:A46)
B47	=SUM($A$2:A47)
B48	=SUM($A$2:A48)
B49	=SUM($A$2:A49)
B50	=SUM($A$2:A50)
B51	=SUM($A$2:A51)
B52	=SUM($A$2:A52)
B53	=SUM($A$2:A53)
B54	=SUM($A$2:A54)
B55	=SUM($A$2:A55)
B56	=SUM($A$2:A56)
B57	=SUM($A$2:A57)
B58	=SUM($A$2:A58)
B59	=SUM($A$2:A59)
B60	=SUM($A$2:A60)
B61	=SUM($A$2:A61)
B62	=SUM($A$2:A62)
B63	=SUM($A$2:A63)
B64	=SUM($A$2:A64)
B65	=SUM($A$2:A65)
B66	=SUM($A$2:A66)
B67	=SUM($A$2:A67)
B68	=SUM($A$2:A68)
B69	=SUM($A$2:A69)
B70	=SUM($A$2:A70)
B71	=SUM($A$2:A71)
B72	=SUM($A$2:A72)
B73	=SUM($A$2:A73)
B74	=SUM($A$2:A74)
B75	=SUM($A$2:A75)
B76	=SUM($A$2:A76)
B77	=SUM($A$2:A77)
B78	=SUM($A$2:A78)
B79	=SUM($A$2:A79)
B80	=SUM($A$2:A80)
B81	=SUM($A$2:A81)
B82	=SUM($A$2:A82)
B83	=SUM($A$2:A83)
B84	=SUM($A$2:A84)
B85	=SUM($A$2:A85)
B86	=SUM($A$2:A86)
B87	=SUM($A$2:A87)
B88	=SUM($A$2:A88)
B89	=SUM($A$2:A89)
B90	=SUM($A$2:A90)
B91	=SUM($A$2:A91)
B92	=SUM($A$2:A92)
B93	=SUM($A$2:A93)
B94	=SUM($A$2:A94)
B95	=SUM($A$2:A95)
B96	=SUM($A$2:A96)
B97	=SUM($A$2:A97)
B98	=SUM($A$2:A98)
B99	=SUM($A$2:A99)
C2	=LN(B2)
C3	=LN(B3)
C4	=LN(B4)
C5	=LN(B5)
C6	=LN(B6)
C7	=LN(B7)
C8	=LN(B8)
C9	=LN(B9)
C10	=LN(B10)
C11	=LN(B11)
C12	=LN(B12)
C13	=LN(B13)
C14	=LN(B14)
C15	=LN(B15)
C16	=LN(B16)
C17	=LN(B17)
C18	=LN(B18)
C19	=LN(B19)
C20	=LN(B20)
C21	=LN(B21)
C22	=LN(B22)
C23	=LN(B23)
C24	=LN(B24)
C25	=LN(B25)
C26	=LN(B26)
C27	=LN(B27)
C28	=LN(B28)
C29	=LN(B29)
C30	=LN(B30)
C31	=LN(B31)
C32	=LN(B32)
C33	=LN(B33)
C34	=LN(B34)
C35	=LN(B35)
C36	=LN(B36)
C37	=LN(B37)
C38	=LN(B38)
C39	=LN(B39)
C40	=LN(B40)
C41	=LN(B41)
C42	=LN(B42)
C43	=LN(B43)
C44	=LN(B44)
C45	=LN(B45)
C46	=LN(B46)
C47	=LN(B47)
C48	=LN(B48)
C49	=LN(B49)
C50	=LN(B50)
C51	=LN(B51)
C52	=LN(B52)
C53	=LN(B53)
C54	=LN(B54)
C55	=LN(B55)
C56	=LN(B56)
C57	=LN(B57)
C58	=LN(B58)
C59	=LN(B59)
C60	=LN(B60)
C61	=LN(B61)
C62	=LN(B62)
C63	=LN(B63)
C64	=LN(B64)
C65	=LN(B65)
C66	=LN(B66)
C67	=LN(B67)
C68	=LN(B68)
C69	=LN(B69)
C70	=LN(B70)
C71	=LN(B71)
C72	=LN(B72)
C73	=LN(B73)
C74	=LN(B74)
C75	=LN(B75)
C76	=LN(B76)
C77	=LN(B77)
C78	=LN(B78)
C79	=LN(B79)
C80	=LN(B80)
C81	=LN(B81)
C82	=LN(B82)
C83	=LN(B83)
C84	=LN(B84)
C85	=LN(B85)
C86	=LN(B86)
C87	=LN(B87)
C88	=LN(B88)
C89	=LN(B89)
C90	=LN(B90)
C91	=LN(B91)
C92	=LN(B92)
C93	=LN(B93)
C94	=LN(B94)
C95	=LN(B95)
C96	=LN(B96)
C97	=LN(B97)
C98	=LN(B98)
C99	=LN(B99)
E2	=ROUND(EXP(Sheet1!L2),0)
E3	=ROUND(EXP(Sheet1!L3),0)
E4	=ROUND(EXP(Sheet1!L4),0)
E5	=ROUND(EXP(Sheet1!L5),0)
E6	=ROUND(EXP(Sheet1!L6),0)
E7	=ROUND(EXP(Sheet1!L7),0)
E8	=ROUND(EXP(Sheet1!L8),0)
E9	=ROUND(EXP(Sheet1!L9),0)
E10	=ROUND(EXP(Sheet1!L10),0)
E11	=ROUND(EXP(Sheet1!L11),0)
E12	=ROUND(EXP(Sheet1!L12),0)
E13	=ROUND(EXP(Sheet1!L13),0)
E14	=ROUND(EXP(Sheet1!L14),0)
E15	=ROUND(EXP(Sheet1!L15),0)
E16	=ROUND(EXP(Sheet1!L16),0)
E17	=ROUND(EXP(Sheet1!L17),0)
E18	=ROUND(EXP(Sheet1!L18),0)
E19	=ROUND(EXP(Sheet1!L19),0)
E20	=ROUND(EXP(Sheet1!L20),0)
E21	=ROUND(EXP(Sheet1!L21),0)
E22	=ROUND(EXP(Sheet1!L22),0)
E23	=ROUND(EXP(Sheet1!L23),0)
E24	=ROUND(EXP(Sheet1!L24),0)
E25	=ROUND(EXP(Sheet1!L25),0)
E26	=ROUND(EXP(Sheet1!L26),0)
E27	=ROUND(EXP(Sheet1!L27),0)
E28	=ROUND(EXP(Sheet1!L28),0)
E29	=ROUND(EXP(Sheet1!L29),0)
E30	=ROUND(EXP(Sheet1!L30),0)
E31	=ROUND(EXP(Sheet1!L31),0)
E32	=ROUND(EXP(Sheet1!L32),0)
E33	=ROUND(EXP(Sheet1!L33),0)
E34	=ROUND(EXP(Sheet1!L34),0)
E35	=ROUND(EXP(Sheet1!L35),0)
E36	=ROUND(EXP(Sheet1!L36),0)
E37	=ROUND(EXP(Sheet1!L37),0)
E38	=ROUND(EXP(Sheet1!L38),0)
E39	=ROUND(EXP(Sheet1!L39),0)
E40	=ROUND(EXP(Sheet1!L40),0)
E41	=ROUND(EXP(Sheet1!L41),0)
E42	=ROUND(EXP(Sheet1!L42),0)
E43	=ROUND(EXP(Sheet1!L43),0)
E44	=ROUND(EXP(Sheet1!L44),0)
E45	=ROUND(EXP(Sheet1!L45),0)
E46	=ROUND(EXP(Sheet1!L46),0)
E47	=ROUND(EXP(Sheet1!L47),0)
E48	=ROUND(EXP(Sheet1!L48),0)
E49	=ROUND(EXP(Sheet1!L49),0)
E50	=ROUND(EXP(Sheet1!L50),0)
E51	=ROUND(EXP(Sheet1!L51),0)
E52	=ROUND(EXP(Sheet1!L52),0)
E53	=ROUND(EXP(Sheet1!L53),0)
E54	=ROUND(EXP(Sheet1!L54),0)
E55	=ROUND(EXP(Sheet1!L55),0)
E56	=ROUND(EXP(Sheet1!L56),0)
E57	=ROUND(EXP(Sheet1!L57),0)
E58	=ROUND(EXP(Sheet1!L58),0)
E59	=ROUND(EXP(Sheet1!L59),0)
E60	=ROUND(EXP(Sheet1!L60),0)
E61	=ROUND(EXP(Sheet1!L61),0)
E62	=ROUND(EXP(Sheet1!L62),0)
E63	=ROUND(EXP(Sheet1!L63),0)
E64	=ROUND(EXP(Sheet1!L64),0)
E65	=ROUND(EXP(Sheet1!L65),0)
E66	=ROUND(EXP(Sheet1!L66),0)
E67	=ROUND(EXP(Sheet1!L67),0)
E68	=ROUND(EXP(Sheet1!L68),0)
E69	=ROUND(EXP(Sheet1!L69),0)
E70	=ROUND(EXP(Sheet1!L70),0)
E71	=ROUND(EXP(Sheet1!L71),0)
E72	=ROUND(EXP(Sheet1!L72),0)
E73	=ROUND(EXP(Sheet1!L73),0)
E74	=ROUND(EXP(Sheet1!L74),0)
E75	=ROUND(EXP(Sheet1!L75),0)
E76	=ROUND(EXP(Sheet1!L76),0)
E77	=ROUND(EXP(Sheet1!L77),0)
E78	=ROUND(EXP(Sheet1!L78),0)
E79	=ROUND(EXP(Sheet1!L79),0)
E80	=ROUND(EXP(Sheet1!L80),0)
E81	=ROUND(EXP(Sheet1!L81),0)
E82	=ROUND(EXP(Sheet1!L82),0)
E83	=ROUND(EXP(Sheet1!L83),0)
E84	=ROUND(EXP(Sheet1!L84),0)
E85	=ROUND(EXP(Sheet1!L85),0)
E86	=ROUND(EXP(Sheet1!L86),0)
E87	=ROUND(EXP(Sheet1!L87),0)
E88	=ROUND(EXP(Sheet1!L88),0)
E89	=ROUND(EXP(Sheet1!L89),0)
E90	=ROUND(EXP(Sheet1!L90),0)
E91	=ROUND(EXP(Sheet1!L91),0)
E92	=ROUND(EXP(Sheet1!L92),0)
E93	=ROUND(EXP(Sheet1!L93),0)
E94	=ROUND(EXP(Sheet1!L94),0)
E95	=ROUND(EXP(Sheet1!L95),0)
E96	=ROUND(EXP(Sheet1!L96),0)
E97	=ROUND(EXP(Sheet1!L97),0)
E98	=ROUND(EXP(Sheet1!L98),0)
E99	=ROUND(EXP(Sheet1!L99),0)
F2	=ROUND(EXP(Sheet1!C2),0)
F3	=ROUND(EXP(Sheet1!C3),0)
F4	=ROUND(EXP(Sheet1!C4),0)
F5	=ROUND(EXP(Sheet1!C5),0)
F6	=ROUND(EXP(Sheet1!C6),0)
F7	=ROUND(EXP(Sheet1!C7),0)
F8	=ROUND(EXP(Sheet1!C8),0)
F9	=ROUND(EXP(Sheet1!C9),0)
F10	=ROUND(EXP(Sheet1!C10),0)
F11	=ROUND(EXP(Sheet1!C11),0)
F12	=ROUND(EXP(Sheet1!C12),0)
F13	=ROUND(EXP(Sheet1!C13),0)
F14	=ROUND(EXP(Sheet1!C14),0)
F15	=ROUND(EXP(Sheet1!C15),0)
F16	=ROUND(EXP(Sheet1!C16),0)
F17	=ROUND(EXP(Sheet1!C17),0)
F18	=ROUND(EXP(Sheet1!C18),0)
F19	=ROUND(EXP(Sheet1!C19),0)
F20	=ROUND(EXP(Sheet1!C20),0)
F21	=ROUND(EXP(Sheet1!C21),0)
F22	=ROUND(EXP(Sheet1!C22),0)
F23	=ROUND(EXP(Sheet1!C23),0)
F24	=ROUND(EXP(Sheet1!C24),0)
F25	=ROUND(EXP(Sheet1!C25),0)
F26	=ROUND(EXP(Sheet1!C26),0)
F27	=ROUND(EXP(Sheet1!C27),0)
F28	=ROUND(EXP(Sheet1!C28),0)
F29	=ROUND(EXP(Sheet1!C29),0)
F30	=ROUND(EXP(Sheet1!C30),0)
F31	=ROUND(EXP(Sheet1!C31),0)
F32	=ROUND(EXP(Sheet1!C32),0)
F33	=ROUND(EXP(Sheet1!C33),0)
F34	=ROUND(EXP(Sheet1!C34),0)
F35	=ROUND(EXP(Sheet1!C35),0)
F36	=ROUND(EXP(Sheet1!C36),0)
F37	=ROUND(EXP(Sheet1!C37),0)
F38	=ROUND(EXP(Sheet1!C38),0)
F39	=ROUND(EXP(Sheet1!C39),0)
F40	=ROUND(EXP(Sheet1!C40),0)
F41	=ROUND(EXP(Sheet1!C41),0)
F42	=ROUND(EXP(Sheet1!C42),0)
F43	=ROUND(EXP(Sheet1!C43),0)
F44	=ROUND(EXP(Sheet1!C44),0)
F45	=ROUND(EXP(Sheet1!C45),0)
F46	=ROUND(EXP(Sheet1!C46),0)
F47	=ROUND(EXP(Sheet1!C47),0)
F48	=ROUND(EXP(Sheet1!C48),0)
F49	=ROUND(EXP(Sheet1!C49),0)
F50	=ROUND(EXP(Sheet1!C50),0)
F51	=ROUND(EXP(Sheet1!C51),0)
F52	=ROUND(EXP(Sheet1!C52),0)
F53	=ROUND(EXP(Sheet1!C53),0)
F54	=ROUND(EXP(Sheet1!C54),0)
F55	=ROUND(EXP(Sheet1!C55),0)
F56	=ROUND(EXP(Sheet1!C56),0)
F57	=ROUND(EXP(Sheet1!C57),0)
F58	=ROUND(EXP(Sheet1!C58),0)
F59	=ROUND(EXP(Sheet1!C59),0)
F60	=ROUND(EXP(Sheet1!C60),0)
F61	=ROUND(EXP(Sheet1!C61),0)
F62	=ROUND(EXP(Sheet1!C62),0)
F63	=ROUND(EXP(Sheet1!C63),0)
F64	=ROUND(EXP(Sheet1!C64),0)
F65	=ROUND(EXP(Sheet1!C65),0)
F66	=ROUND(EXP(Sheet1!C66),0)
F67	=ROUND(EXP(Sheet1!C67),0)
F68	=ROUND(EXP(Sheet1!C68),0)
F69	=ROUND(EXP(Sheet1!C69),0)
F70	=ROUND(EXP(Sheet1!C70),0)
F71	=ROUND(EXP(Sheet1!C71),0)
F72	=ROUND(EXP(Sheet1!C72),0)
F73	=ROUND(EXP(Sheet1!C73),0)
F74	=ROUND(EXP(Sheet1!C74),0)
F75	=ROUND(EXP(Sheet1!C75),0)
F76	=ROUND(EXP(Sheet1!C76),0)
F77	=ROUND(EXP(Sheet1!C77),0)
F78	=ROUND(EXP(Sheet1!C78),0)
F79	=ROUND(EXP(Sheet1!C79),0)
F80	=ROUND(EXP(Sheet1!C80),0)
F81	=ROUND(EXP(Sheet1!C81),0)
F82	=ROUND(EXP(Sheet1!C82),0)
F83	=ROUND(EXP(Sheet1!C83),0)
F84	=ROUND(EXP(Sheet1!C84),0)
F85	=ROUND(EXP(Sheet1!C85),0)
F86	=ROUND(EXP(Sheet1!C86),0)
F87	=ROUND(EXP(Sheet1!C87),0)
F88	=ROUND(EXP(Sheet1!C88),0)
F89	=ROUND(EXP(Sheet1!C89),0)
F90	=ROUND(EXP(Sheet1!C90),0)
F91	=ROUND(EXP(Sheet1!C91),0)
F92	=ROUND(EXP(Sheet1!C92),0)
F93	=ROUND(EXP(Sheet1!C93),0)
F94	=ROUND(EXP(Sheet1!C94),0)
F95	=ROUND(EXP(Sheet1!C95),0)
F96	=ROUND(EXP(Sheet1!C96),0)
F97	=ROUND(EXP(Sheet1!C97),0)
F98	=ROUND(EXP(Sheet1!C98),0)
F99	=ROUND(EXP(Sheet1!C99),0)
G2	=ROUND(EXP(Sheet1!M2),0)
G3	=ROUND(EXP(Sheet1!M3),0)
G4	=ROUND(EXP(Sheet1!M4),0)
G5	=ROUND(EXP(Sheet1!M5),0)
G6	=ROUND(EXP(Sheet1!M6),0)
G7	=ROUND(EXP(Sheet1!M7),0)
G8	=ROUND(EXP(Sheet1!M8),0)
G9	=ROUND(EXP(Sheet1!M9),0)
G10	=ROUND(EXP(Sheet1!M10),0)
G11	=ROUND(EXP(Sheet1!M11),0)
G12	=ROUND(EXP(Sheet1!M12),0)
G13	=ROUND(EXP(Sheet1!M13),0)
G14	=ROUND(EXP(Sheet1!M14),0)
G15	=ROUND(EXP(Sheet1!M15),0)
G16	=ROUND(EXP(Sheet1!M16),0)
G17	=ROUND(EXP(Sheet1!M17),0)
G18	=ROUND(EXP(Sheet1!M18),0)
G19	=ROUND(EXP(Sheet1!M19),0)
G20	=ROUND(EXP(Sheet1!M20),0)
G21	=ROUND(EXP(Sheet1!M21),0)
G22	=ROUND(EXP(Sheet1!M22),0)
G23	=ROUND(EXP(Sheet1!M23),0)
G24	=ROUND(EXP(Sheet1!M24),0)
G25	=ROUND(EXP(Sheet1!M25),0)
G26	=ROUND(EXP(Sheet1!M26),0)
G27	=ROUND(EXP(Sheet1!M27),0)
G28	=ROUND(EXP(Sheet1!M28),0)
G29	=ROUND(EXP(Sheet1!M29),0)
G30	=ROUND(EXP(Sheet1!M30),0)
G31	=ROUND(EXP(Sheet1!M31),0)
G32	=ROUND(EXP(Sheet1!M32),0)
G33	=ROUND(EXP(Sheet1!M33),0)
G34	=ROUND(EXP(Sheet1!M34),0)
G35	=ROUND(EXP(Sheet1!M35),0)
G36	=ROUND(EXP(Sheet1!M36),0)
G37	=ROUND(EXP(Sheet1!M37),0)
G38	=ROUND(EXP(Sheet1!M38),0)
G39	=ROUND(EXP(Sheet1!M39),0)
G40	=ROUND(EXP(Sheet1!M40),0)
G41	=ROUND(EXP(Sheet1!M41),0)
G42	=ROUND(EXP(Sheet1!M42),0)
G43	=ROUND(EXP(Sheet1!M43),0)
G44	=ROUND(EXP(Sheet1!M44),0)
G45	=ROUND(EXP(Sheet1!M45),0)
G46	=ROUND(EXP(Sheet1!M46),0)
G47	=ROUND(EXP(Sheet1!M47),0)
G48	=ROUND(EXP(Sheet1!M48),0)
G49	=ROUND(EXP(Sheet1!M49),0)
G50	=ROUND(EXP(Sheet1!M50),0)
G51	=ROUND(EXP(Sheet1!M51),0)
G52	=ROUND(EXP(Sheet1!M52),0)
G53	=ROUND(EXP(Sheet1!M53),0)
G54	=ROUND(EXP(Sheet1!M54),0)
G55	=ROUND(EXP(Sheet1!M55),0)
G56	=ROUND(EXP(Sheet1!M56),0)
G57	=ROUND(EXP(Sheet1!M57),0)
G58	=ROUND(EXP(Sheet1!M58),0)
G59	=ROUND(EXP(Sheet1!M59),0)
G60	=ROUND(EXP(Sheet1!M60),0)
G61	=ROUND(EXP(Sheet1!M61),0)
G62	=ROUND(EXP(Sheet1!M62),0)
G63	=ROUND(EXP(Sheet1!M63),0)
G64	=ROUND(EXP(Sheet1!M64),0)
G65	=ROUND(EXP(Sheet1!M65),0)
G66	=ROUND(EXP(Sheet1!M66),0)
G67	=ROUND(EXP(Sheet1!M67),0)
G68	=ROUND(EXP(Sheet1!M68),0)
G69	=ROUND(EXP(Sheet1!M69),0)
G70	=ROUND(EXP(Sheet1!M70),0)
G71	=ROUND(EXP(Sheet1!M71),0)
G72	=ROUND(EXP(Sheet1!M72),0)
G73	=ROUND(EXP(Sheet1!M73),0)
G74	=ROUND(EXP(Sheet1!M74),0)
G75	=ROUND(EXP(Sheet1!M75),0)
G76	=ROUND(EXP(Sheet1!M76),0)
G77	=ROUND(EXP(Sheet1!M77),0)
G78	=ROUND(EXP(Sheet1!M78),0)
G79	=ROUND(EXP(Sheet1!M79),0)
G80	=ROUND(EXP(Sheet1!M80),0)
G81	=ROUND(EXP(Sheet1!M81),0)
G82	=ROUND(EXP(Sheet1!M82),0)
G83	=ROUND(EXP(Sheet1!M83),0)
G84	=ROUND(EXP(Sheet1!M84),0)
G85	=ROUND(EXP(Sheet1!M85),0)
G86	=ROUND(EXP(Sheet1!M86),0)
G87	=ROUND(EXP(Sheet1!M87),0)
G88	=ROUND(EXP(Sheet1!M88),0)
G89	=ROUND(EXP(Sheet1!M89),0)
G90	=ROUND(EXP(Sheet1!M90),0)
G91	=ROUND(EXP(Sheet1!M91),0)
G92	=ROUND(EXP(Sheet1!M92),0)
G93	=ROUND(EXP(Sheet1!M93),0)
G94	=ROUND(EXP(Sheet1!M94),0)
G95	=ROUND(EXP(Sheet1!M95),0)
G96	=ROUND(EXP(Sheet1!M96),0)
G97	=ROUND(EXP(Sheet1!M97),0)
G98	=ROUND(EXP(Sheet1!M98),0)
G99	=ROUND(EXP(Sheet1!M99),0)
I2	=ROUND(E2-B2,0)
I3	=ROUND(E3-B3,0)
I4	=ROUND(E4-B4,0)
I5	=ROUND(E5-B5,0)
I6	=ROUND(E6-B6,0)
I7	=ROUND(E7-B7,0)
I8	=ROUND(E8-B8,0)
I9	=ROUND(E9-B9,0)
I10	=ROUND(E10-B10,0)
I11	=ROUND(E11-B11,0)
I12	=ROUND(E12-B12,0)
I13	=ROUND(E13-B13,0)
I14	=ROUND(E14-B14,0)
I15	=ROUND(E15-B15,0)
I16	=ROUND(E16-B16,0)
I17	=ROUND(E17-B17,0)
I18	=ROUND(E18-B18,0)
I19	=ROUND(E19-B19,0)
I20	=ROUND(E20-B20,0)
I21	=ROUND(E21-B21,0)
I22	=ROUND(E22-B22,0)
I23	=ROUND(E23-B23,0)
I24	=ROUND(E24-B24,0)
I25	=ROUND(E25-B25,0)
I26	=ROUND(E26-B26,0)
I27	=ROUND(E27-B27,0)
I28	=ROUND(E28-B28,0)
I29	=ROUND(E29-B29,0)
I30	=ROUND(E30-B30,0)
I31	=ROUND(E31-B31,0)
I32	=ROUND(E32-B32,0)
I33	=ROUND(E33-B33,0)
I34	=ROUND(E34-B34,0)
I35	=ROUND(E35-B35,0)
I36	=ROUND(E36-B36,0)
I37	=ROUND(E37-B37,0)
I38	=ROUND(E38-B38,0)
I39	=ROUND(E39-B39,0)
I40	=ROUND(E40-B40,0)
I41	=ROUND(E41-B41,0)
I42	=ROUND(E42-B42,0)
I43	=ROUND(E43-B43,0)
I44	=ROUND(E44-B44,0)
I45	=ROUND(E45-B45,0)
I46	=ROUND(E46-B46,0)
I47	=ROUND(E47-B47,0)
I48	=ROUND(E48-B48,0)
I49	=ROUND(E49-B49,0)
I50	=ROUND(E50-B50,0)
I51	=ROUND(E51-B51,0)
I52	=ROUND(E52-B52,0)
I53	=ROUND(E53-B53,0)
I54	=ROUND(E54-B54,0)
I55	=ROUND(E55-B55,0)
I56	=ROUND(E56-B56,0)
I57	=ROUND(E57-B57,0)
I58	=ROUND(E58-B58,0)
I59	=ROUND(E59-B59,0)
I60	=ROUND(E60-B60,0)
I61	=ROUND(E61-B61,0)
I62	=ROUND(E62-B62,0)
I63	=ROUND(E63-B63,0)
I64	=ROUND(E64-B64,0)
I65	=ROUND(E65-B65,0)
I66	=ROUND(E66-B66,0)
I67	=ROUND(E67-B67,0)
I68	=ROUND(E68-B68,0)
I69	=ROUND(E69-B69,0)
I70	=ROUND(E70-B70,0)
I71	=ROUND(E71-B71,0)
I72	=ROUND(E72-B72,0)
I73	=ROUND(E73-B73,0)
I74	=ROUND(E74-B74,0)
I75	=ROUND(E75-B75,0)
I76	=ROUND(E76-B76,0)
I77	=ROUND(E77-B77,0)
I78	=ROUND(E78-B78,0)
I79	=ROUND(E79-B79,0)
I80	=ROUND(E80-B80,0)
I81	=ROUND(E81-B81,0)
I82	=ROUND(E82-B82,0)
I83	=ROUND(E83-B83,0)
I84	=ROUND(E84-B84,0)
I85	=ROUND(E85-B85,0)
I86	=ROUND(E86-B86,0)
I87	=ROUND(E87-B87,0)
I88	=ROUND(E88-B88,0)
I89	=ROUND(E89-B89,0)
I90	=ROUND(E90-B90,0)
I91	=ROUND(E91-B91,0)
I92	=ROUND(E92-B92,0)
I93	=ROUND(E93-B93,0)
I94	=ROUND(E94-B94,0)
I95	=ROUND(E95-B95,0)
I96	=ROUND(E96-B96,0)
I97	=ROUND(E97-B97,0)
I98	=ROUND(E98-B98,0)
I99	=ROUND(E99-B99,0)
J2	=ROUND(B2-F2,0)
J3	=ROUND(B3-F3,0)
J4	=ROUND(B4-F4,0)
J5	=ROUND(B5-F5,0)
J6	=ROUND(B6-F6,0)
J7	=ROUND(B7-F7,0)
J8	=ROUND(B8-F8,0)
J9	=ROUND(B9-F9,0)
J10	=ROUND(B10-F10,0)
J11	=ROUND(B11-F11,0)
J12	=ROUND(B12-F12,0)
J13	=ROUND(B13-F13,0)
J14	=ROUND(B14-F14,0)
J15	=ROUND(B15-F15,0)
J16	=ROUND(B16-F16,0)
J17	=ROUND(B17-F17,0)
J18	=ROUND(B18-F18,0)
J19	=ROUND(B19-F19,0)
J20	=ROUND(B20-F20,0)
J21	=ROUND(B21-F21,0)
J22	=ROUND(B22-F22,0)
J23	=ROUND(B23-F23,0)
J24	=ROUND(B24-F24,0)
J25	=ROUND(B25-F25,0)
J26	=ROUND(B26-F26,0)
J27	=ROUND(B27-F27,0)
J28	=ROUND(B28-F28,0)
J29	=ROUND(B29-F29,0)
J30	=ROUND(B30-F30,0)
J31	=ROUND(B31-F31,0)
J32	=ROUND(B32-F32,0)
J33	=ROUND(B33-F33,0)
J34	=ROUND(B34-F34,0)
J35	=ROUND(B35-F35,0)
J36	=ROUND(B36-F36,0)
J37	=ROUND(B37-F37,0)
J38	=ROUND(B38-F38,0)
J39	=ROUND(B39-F39,0)
J40	=ROUND(B40-F40,0)
J41	=ROUND(B41-F41,0)
J42	=ROUND(B42-F42,0)
J43	=ROUND(B43-F43,0)
J44	=ROUND(B44-F44,0)
J45	=ROUND(B45-F45,0)
J46	=ROUND(B46-F46,0)
J47	=ROUND(B47-F47,0)
J48	=ROUND(B48-F48,0)
J49	=ROUND(B49-F49,0)
J50	=ROUND(B50-F50,0)
J51	=ROUND(B51-F51,0)
J52	=ROUND(B52-F52,0)
J53	=ROUND(B53-F53,0)
J54	=ROUND(B54-F54,0)
J55	=ROUND(B55-F55,0)
J56	=ROUND(B56-F56,0)
J57	=ROUND(B57-F57,0)
J58	=ROUND(B58-F58,0)
J59	=ROUND(B59-F59,0)
J60	=ROUND(B60-F60,0)
J61	=ROUND(B61-F61,0)
J62	=ROUND(B62-F62,0)
J63	=ROUND(B63-F63,0)
J64	=ROUND(B64-F64,0)
J65	=ROUND(B65-F65,0)
J66	=ROUND(B66-F66,0)
J67	=ROUND(B67-F67,0)
J68	=ROUND(B68-F68,0)
J69	=ROUND(B69-F69,0)
J70	=ROUND(B70-F70,0)
J71	=ROUND(B71-F71,0)
J72	=ROUND(B72-F72,0)
J73	=ROUND(B73-F73,0)
J74	=ROUND(B74-F74,0)
J75	=ROUND(B75-F75,0)
J76	=ROUND(B76-F76,0)
J77	=ROUND(B77-F77,0)
J78	=ROUND(B78-F78,0)
J79	=ROUND(B79-F79,0)
J80	=ROUND(B80-F80,0)
J81	=ROUND(B81-F81,0)
J82	=ROUND(B82-F82,0)
J83	=ROUND(B83-F83,0)
J84	=ROUND(B84-F84,0)
J85	=ROUND(B85-F85,0)
J86	=ROUND(B86-F86,0)
J87	=ROUND(B87-F87,0)
J88	=ROUND(B88-F88,0)
J89	=ROUND(B89-F89,0)
J90	=ROUND(B90-F90,0)
J91	=ROUND(B91-F91,0)
J92	=ROUND(B92-F92,0)
J93	=ROUND(B93-F93,0)
J94	=ROUND(B94-F94,0)
J95	=ROUND(B95-F95,0)
J96	=ROUND(B96-F96,0)
J97	=ROUND(B97-F97,0)
J98	=ROUND(B98-F98,0)
J99	=ROUND(B99-F99,0)
K2	=ROUND(G2-B2,0)
K3	=ROUND(G3-B3,0)
K4	=ROUND(G4-B4,0)
K5	=ROUND(G5-B5,0)
K6	=ROUND(G6-B6,0)
K7	=ROUND(G7-B7,0)
K8	=ROUND(G8-B8,0)
K9	=ROUND(G9-B9,0)
K10	=ROUND(G10-B10,0)
K11	=ROUND(G11-B11,0)
K12	=ROUND(G12-B12,0)
K13	=ROUND(G13-B13,0)
K14	=ROUND(G14-B14,0)
K15	=ROUND(G15-B15,0)
K16	=ROUND(G16-B16,0)
K17	=ROUND(G17-B17,0)
K18	=ROUND(G18-B18,0)
K19	=ROUND(G19-B19,0)
K20	=ROUND(G20-B20,0)
K21	=ROUND(G21-B21,0)
K22	=ROUND(G22-B22,0)
K23	=ROUND(G23-B23,0)
K24	=ROUND(G24-B24,0)
K25	=ROUND(G25-B25,0)
K26	=ROUND(G26-B26,0)
K27	=ROUND(G27-B27,0)
K28	=ROUND(G28-B28,0)
K29	=ROUND(G29-B29,0)
K30	=ROUND(G30-B30,0)
K31	=ROUND(G31-B31,0)
K32	=ROUND(G32-B32,0)
K33	=ROUND(G33-B33,0)
K34	=ROUND(G34-B34,0)
K35	=ROUND(G35-B35,0)
K36	=ROUND(G36-B36,0)
K37	=ROUND(G37-B37,0)
K38	=ROUND(G38-B38,0)
K39	=ROUND(G39-B39,0)
K40	=ROUND(G40-B40,0)
K41	=ROUND(G41-B41,0)
K42	=ROUND(G42-B42,0)
K43	=ROUND(G43-B43,0)
K44	=ROUND(G44-B44,0)
K45	=ROUND(G45-B45,0)
K46	=ROUND(G46-B46,0)
K47	=ROUND(G47-B47,0)
K48	=ROUND(G48-B48,0)
K49	=ROUND(G49-B49,0)
K50	=ROUND(G50-B50,0)
K51	=ROUND(G51-B51,0)
K52	=ROUND(G52-B52,0)
K53	=ROUND(G53-B53,0)
K54	=ROUND(G54-B54,0)
K55	=ROUND(G55-B55,0)
K56	=ROUND(G56-B56,0)
K57	=ROUND(G57-B57,0)
K58	=ROUND(G58-B58,0)
K59	=ROUND(G59-B59,0)
K60	=ROUND(G60-B60,0)
K61	=ROUND(G61-B61,0)
K62	=ROUND(G62-B62,0)
K63	=ROUND(G63-B63,0)
K64	=ROUND(G64-B64,0)
K65	=ROUND(G65-B65,0)
K66	=ROUND(G66-B66,0)
K67	=ROUND(G67-B67,0)
K68	=ROUND(G68-B68,0)
K69	=ROUND(G69-B69,0)
K70	=ROUND(G70-B70,0)
K71	=ROUND(G71-B71,0)
K72	=ROUND(G72-B72,0)
K73	=ROUND(G73-B73,0)
K74	=ROUND(G74-B74,0)
K75	=ROUND(G75-B75,0)
K76	=ROUND(G76-B76,0)
K77	=ROUND(G77-B77,0)
K78	=ROUND(G78-B78,0)
K79	=ROUND(G79-B79,0)
K80	=ROUND(G80-B80,0)
K81	=ROUND(G81-B81,0)
K82	=ROUND(G82-B82,0)
K83	=ROUND(G83-B83,0)
K84	=ROUND(G84-B84,0)
K85	=ROUND(G85-B85,0)
K86	=ROUND(G86-B86,0)
K87	=ROUND(G87-B87,0)
K88	=ROUND(G88-B88,0)
K89	=ROUND(G89-B89,0)
K90	=ROUND(G90-B90,0)
K91	=ROUND(G91-B91,0)
K92	=ROUND(G92-B92,0)
K93	=ROUND(G93-B93,0)
K94	=ROUND(G94-B94,0)
K95	=ROUND(G95-B95,0)
K96	=ROUND(G96-B96,0)
K97	=ROUND(G97-B97,0)
K98	=ROUND(G98-B98,0)
K99	=ROUND(G99-B99,0)

Macros:

Sub Minimize_Smoothing_Constant()
Dim i As Single, RowNumber As Integer
RowNumber = 12
With ActiveSheet
    For i = 0.1 To 1 Step 0.1
        .Cells(3, 5) = i
        .Cells(RowNumber, 5) = i
        .Cells(RowNumber, 6) = .Cells(6, 6)
        RowNumber = RowNumber + 1
    Next i
End With
End Sub

Option Explicit

Private Sub SpinButton1_Change()
SpinButton1.Max = 20
SpinButton1.Min = 12
Range("E3").Value = Range("E" & SpinButton1.Value).Value
Range("F6").Value = Range("E" & SpinButton1.Value).Offset(0, 1).Value
End Sub

Private Sub SpinButton2_Change()
SpinButton2.Max = 28
SpinButton2.Min = 4
Range("E6").Value = Range("H" & SpinButton2.Value).Value
End Sub

There are two spin buttons on sheet 1 that allow you to select the smoothing constant, and the confidence interval.

 

[michaelsmith559]

This may be easier to see the results of what I did:

Excel 2012
	A	B	C
1	Week	Actual	Actual Forecast
2	1	10	0
3	2	8	8
4	3	7	8
5	4	7	8
6	5	5	6
7	6	1	2
8	7	3	3
9	8	1	1
10	9	10	10
11	10	3	4
12	11	6	6
13	12	1	2
14	13	3	3
15	14	21	21
16	15	11	13
17	16	5	6
18	17	5	6
19	18	10	11
20	19	50	51
21	20	57	63
22	21	34	41
23	22	49	53
24	23	277	283
25	24	172	209
26	25	170	194
27	26	175	197
28	27	520	541
29	28	3	68
30	29	58	65
31	30	66	73
32	31	40	47
33	32	57	62
34	33	323	329
35	34	200	235
36	35	198	223
37	36	204	227
38	37	607	631
39	38	4	73
40	39	58	65
41	40	66	73
42	41	40	47
43	42	57	62
44	43	323	329
45	44	200	234
46	45	198	222
47	46	204	227
48	47	607	630
49	48	4	71
50	49	22	29
51	50	325	328
52	51	495	529
53	52	458	513
54	53	517	570
55	54	868	927
56	55	728	826
57	56	701	787
58	57	759	841
59	58	2078	2165
60	59	13	249
61	60	2	27
62	61	179	182
63	62	213	231
64	63	122	145
65	64	173	188
66	65	1016	1035
67	66	619	726
68	67	654	728
69	68	636	710
70	69	1792	1865
71	70	12	208
72	71	28	49
73	72	55	60
74	73	118	124
75	74	54	66
76	75	133	140
77	76	55	69
78	77	120	127
79	78	45	58
80	79	462	468
81	80	14	61
82	81	157	163
83	82	135	151
84	83	146	161
85	84	131	147
86	85	546	561
87	86	725	782
88	87	271	351
89	88	729	764
90	89	2082	2160
91	90	23	248
92	91	10	35
93	92	65	68
94	93	111	118
95	94	32	44
96	95	79	83
97	96	562	570
98	97	99	157
99	98	17	33
Sheet3

 

[michaelsmith559]

Sorry for all of the formulas in the above post, I clicked the wrong formula option.

 

[michaelsmith559]

This is the formula for cell f6 on sheet 1:

=SQRT(SUMXMY2(OFFSET($B$2,0,0,COUNTA($B:$B),-1),OFFSET($C$2,0,0,COUNTA($C:$C),-1))/COUNT(OFFSET($B$2,0,0,COUNTA($B:$B),-1)))

 

[natekris8183]

I certainly started off with an aggregate column to keep a running total of the data and have thought about breaking it out mathematically; however, this information needs to be fairly universal as I will not always be the one inputting. My gut was to develop a UDF to loop through each row of the table where when the SKU matches the SKU at the current row it would create an array of Y values and X's values and propose the new X value as the date at the current row. I can perform this very act through building the array through a conjoining statement such as ()*() with the multiple qualifiers, except it reduces the False values to "0". I worked at trying to develop the array through a function inside the worksheet, I just don't see that being possible. A loop with a Dim Long to build the necessary arrays to input into the Application.WorksheetFunction.Trend seems like a scenario that would be suitable for future use. What are you thoughts? This would avoid the non-linear setup of the data or at the least negate it.

I do thank you very much for the mathematical solution, and I have developed a version similar, I just was trying to avoid the multitude of creating various ancillary columns for statistical analysis purposes.

 

[michaelsmith559]

I'm not sure how to go about what you are wanting to do if not something like this. Maybe someone else on here will be able to do something with arrays to accomplish what you want. Good luck.

Mike

 

[natekris8183]

I do greatly appreciate the solution Mike. It definitely an option I am going to utilize should I not be able to complete a loop function to use within the WorksheetFunction.Trend.

 

[michaelsmith559]

Glad to help. Hope you get it figured out.

Mike

 

[natekris8183]

This is an example of what I am wanting to do. The problem is that the WorksheetFunction.Trend isn't recognizing the ActualList and the DateList as valid arguments for the known Y's and X's.

Dim tbl As ListObject
Dim curDate As Date
Dim ActualList As Long
Dim Datelist As Long

Set tbl = ActiveSheet.ListObjects("RawSalesDataTbl")
curDate = Cells(ActiveCell.Row, Range("RawSalesDataTbl[Week '#]").Column).Value
curSKU = Cells(ActiveCell.Row, Range("RawSalesDataTbl[SKU]").Column).Value
curSource = Cells(ActiveCell.Row, Range("RawSalesDataTbl[Source]").Column).Value

    MsgBox curDate & " " & curSKU
    
For i = 2 To tbl.Range.Rows.Count

    If Cells(i, Range("RawSalesDataTbl[Week '#]").Column).Value <= curDate Then
        
        If Cells(i, Range("RawSalesDataTbl[SKU]").Column).Value = curSKU Then
        
            If Cells(i, Range("RawSalesDataTbl[Source]").Column).Value = curSource Then

                           
                ActualList = Cells(i, Range("RawSalesDataTbl[Actual]").Column).Value
                Datelist = Cells(i, Range("RawSalesDataTbl[Week '#]").Column).Value
                
            
            End If
            
        End If
        
    End If
    
Next i

MsgBox Application.WorksheetFunction.Trend(ActualList, Datelist, curDate)

The goal will be the set a UDF to this macro to input into the trend column of the table.

 

[michaelsmith559]

Just looking back over your macro. Have a couple of questions, and one suggestion, just to see what happens.

Try this code:

Sub VBA_Trend_Forecast()
Dim tbl As ListObject
Dim curDate As Date
Dim ActualList As Long
Dim Datelist As Long

Set tbl = ActiveSheet.ListObjects("RawSalesDataTbl")
curDate = Cells(ActiveCell.Row, Range("RawSalesDataTbl[Week '#]").Column).Value
curSKU = Cells(ActiveCell.Row, Range("RawSalesDataTbl[SKU]").Column).Value
curSource = Cells(ActiveCell.Row, Range("RawSalesDataTbl[Source]").Column).Value

    MsgBox curDate & " " & curSKU
    
For i = 2 To tbl.Range.Rows.Count

    If Cells(i, Range("RawSalesDataTbl[Week '#]").Column).Value <= curDate Then
        
        If Cells(i, Range("RawSalesDataTbl[SKU]").Column).Value = curSKU Then
        
            If Cells(i, Range("RawSalesDataTbl[Source]").Column).Value = curSource Then

                           
                ActualList = Cells(i, Range("RawSalesDataTbl[Actual]").Column).Value
                Datelist = Cells(i, Range("RawSalesDataTbl[Week '#]").Column).Value
                MsgBox Application.WorksheetFunction.Trend(ActualList, Datelist, curDate)
            
            End If
            
        End If
        
    End If
    
Next i

End Sub

Are you trying to use the trend function in vba instead of the formula in the worksheet? It seems like to me your macro is not giving you the trend in the message box because it is still looping through the individual cells. I think your acuallist and datelist need to be adjusted for the entire column each, if this makes sense.

Just a thought.

Mike