{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Mean & Standard Deviation","X Y Scatter","Covariance and Correlation","Slope & Int","C. of Determination r^2","Test Significance"],"not_rendered":{"charts":5,"images":1,"macros":0,"external_links":1,"defined_names":0,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Mean & Standard Deviation","index":0,"n_rows":25,"n_cols":5,"rows_kept":25,"cut":false,"hidden":false,"merged":[[11,1,11,2]]}
["Manager needs to predict Weekly Sales"]
["Manager looks at Past Data:"]
["Weekly Sales\n(Past Data) Y"]
[97000]
[185000]
[260000]
[143000]
[270000]
[398000]
[]
["Manager wants to look at avergae and varaition in the data."]
["Weekly Sales\n(Past Data)","Deviation from Mean\n(Particular Value - Mean)",null,"Deviation^2"]
[{"$f":"A4","v":97000},{"$f":"IF(A13<100000,0,\"\")&FIXED(A13,0)&\" - \"&FIXED($B$21,0)&\" =\"","v":"097,000 - 225,500 ="},{"$f":"A13-$B$21","v":-128500},{"$f":"C13^2","v":16512250000}]
[185000,"185,000 - 225,500 =",-40500,1640250000]
[260000,"260,000 - 225,500 =",34500,1190250000]
[143000,"143,000 - 225,500 =",-82500,6806250000]
[270000,"270,000 - 225,500 =",44500,1980250000]
[398000,"398,000 - 225,500 =",172500,29756250000]
[null,"Total",{"$f":"SUM(C13:C18)","v":0},{"$f":"SUM(D13:D18)","v":57885500000}]
[]
["Mean = Xbar =",{"$f":"AVERAGE(A13:A18)","v":225500},{"$f":"\" \"&IF(_xlfn.ISFORMULA(B21),_xlfn.FORMULATEXT(B21),\"\")","v":" =AVERAGE(A13:A18)"}]
["Count = n =",{"$f":"COUNT(A13:A18)","v":6}," =COUNT(A13:A18)",null,"    "]
["n - 1",{"$f":"B22-1","v":5}," =B22-1"]
["Standard Deviation",{"$f":"SQRT(D19/B23)","v":107596.9330417926}," =SQRT(D19/B23)"]
["Standard Deviation",{"$f":"_xlfn.STDEV.S(A13:A18)","v":107596.9330417926}," =STDEV.S(A13:A18)"]

{"sheet":"X Y Scatter","index":1,"n_rows":12,"n_cols":4,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
["Ad $ Spent per Week and Weekly Sales"]
[]
["X\nAd Dollars Spent per Week","Y\nWeekly Sales"]
[14000,97000]
[27000,185000]
[39900,260000]
[17000,143000]
[34000,270000]
[43000,398000]
[]
[]
[null,null,null,"    "]

{"sheet":"Covariance and Correlation","index":2,"n_rows":36,"n_cols":5,"rows_kept":36,"cut":false,"hidden":false,"merged":[]}
["Ad $ Spent per Week and Weekly Sales"]
[]
["Ad Dollars Spent per Week - X","Weekly Sales - Y","X Deviation\n(Xi - Xbar)","Y Devtaion\n(Yi - Ybar)","(Xi - Xbar)*\n(Yi - Ybar)"]
[14000,97000,{"$f":"A4-$B$28","v":-15150},{"$f":"B4-$B$29","v":-128500},{"$f":"D4*C4","v":1946775000}]
[27000,185000,-2150,-40500,87075000]
[39900,260000,10750,34500,370875000]
[17000,143000,-12150,-82500,1002375000]
[34000,270000,4850,44500,215825000]
[43000,398000,13850,172500,2389125000]
[null,null,{"$f":"SUM(C4:C9)","v":0},0,{"$f":"SUM(E4:E9)","v":6012050000}]
[]
["Xbar","Y","X","Ybar"]
[{"$f":"$B$28","v":29150},{"$f":"MIN(B4:B9)","v":97000},{"$f":"MIN(A4:A9)","v":14000},{"$f":"$B$29","v":225500}]
[{"$f":"$B$28","v":29150},{"$f":"MAX(B4:B9)","v":398000},{"$f":"MAX(A4:A9)","v":43000},{"$f":"$B$29","v":225500}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Count = n =",{"$f":"COUNT(A4:A9)","v":6},{"$f":"\" \"&IF(_xlfn.ISFORMULA(B26),_xlfn.FORMULATEXT(B26),\"\")","v":" =COUNT(A4:A9)"}]
["n-1",{"$f":"B26-1","v":5}," =B26-1"]
["Xbar",{"$f":"AVERAGE(A4:A9)","v":29150}," =AVERAGE(A4:A9)"]
["Ybar",{"$f":"AVERAGE(B4:B9)","v":225500}," =AVERAGE(B4:B9)"]
["Sample  Standard Deviation X = sx",{"$f":"_xlfn.STDEV.S(A4:A9)","v":11938.802284986547}," =STDEV.S(A4:A9)"]
["Sample  Standard Deviation Y = sy",{"$f":"_xlfn.STDEV.S(B4:B9)","v":107596.9330417926}," =STDEV.S(B4:B9)"]
["Covariance = sxy",{"$f":"E10/B27","v":1202410000}," =E10/B27"]
["Covariance = sxy",{"$f":"_xlfn.COVARIANCE.S(B4:B9,A4:A9)","v":1202410000}," =COVARIANCE.S(B4:B9,A4:A9)"]
["Coefficient of Correlation = rxy",{"$f":"B32/PRODUCT(B30:B31)","v":0.93603465306822053}," =B32/PRODUCT(B30:B31)"]
["Coefficient of Correlation = rxy",{"$f":"PEARSON(B4:B9,A4:A9)","v":0.93603465306822031}," =PEARSON(B4:B9,A4:A9)"]
["Coefficient of Correlation = rxy",{"$f":"CORREL(B4:B9,A4:A9)","v":0.93603465306822031}," =CORREL(B4:B9,A4:A9)"]

{"sheet":"Slope & Int","index":3,"n_rows":41,"n_cols":10,"rows_kept":41,"cut":false,"hidden":false,"merged":[]}
["Ad $ Spent per Week and Weekly Sales"]
[]
["Ad Dollars Spent per Week - X","Weekly Sales - Y","X Deviation\n(Xi - Xbar)","Y Devtaion\n(Yi - Ybar)","(Xi - Xbar)*\n(Yi - Ybar)","X Deviation^2\n(Xi - Xbar)^2",null,null,null,"        "]
[14000,97000,{"$f":"A4-$B$28","v":-15150},{"$f":"B4-$B$29","v":-128500},{"$f":"D4*C4","v":1946775000},{"$f":"C4^2","v":229522500}]
[27000,185000,-2150,-40500,87075000,{"$f":"C5^2","v":4622500}]
[39900,260000,10750,34500,370875000,115562500]
[17000,143000,-12150,-82500,1002375000,147622500]
[34000,270000,4850,44500,215825000,23522500]
[43000,398000,13850,172500,2389125000,191822500]
[null,null,{"$f":"SUM(C4:C9)","v":0},0,{"$f":"SUM(E4:E9)","v":6012050000},{"$f":"SUM(F4:F9)","v":712675000}]
[]
["Xbar","Y","X","Ybar"]
[{"$f":"$B$28","v":29150},{"$f":"MIN(B4:B9)","v":97000},{"$f":"MIN(A4:A9)","v":14000},{"$f":"$B$29","v":225500}]
[{"$f":"$B$28","v":29150},{"$f":"MAX(B4:B9)","v":398000},{"$f":"MAX(A4:A9)","v":43000},{"$f":"$B$29","v":225500}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Count = n =",{"$f":"COUNT(A4:A9)","v":6},{"$f":"\" \"&IF(_xlfn.ISFORMULA(B26),_xlfn.FORMULATEXT(B26),\"\")","v":" =COUNT(A4:A9)"}]
["n-1",{"$f":"B26-1","v":5}," =B26-1"]
["Xbar",{"$f":"AVERAGE(A4:A9)","v":29150}," =AVERAGE(A4:A9)"]
["Ybar",{"$f":"AVERAGE(B4:B9)","v":225500}," =AVERAGE(B4:B9)"]
["Slope = b1 =",{"$f":"E10/F10","v":8.4358929385764903}," =E10/F10"]
["y-intercept = b0 =",{"$f":"B29-B30*B28","v":-20406.279159504687}," =B29-B30*B28"]
["Slope = b1 =",{"$f":"SLOPE(B4:B9,A4:A9)","v":8.4358929385764903},{"$f":"\" \"&IF(_xlfn.ISFORMULA(B32),_xlfn.FORMULATEXT(B32),\"\")","v":" =SLOPE(B4:B9,A4:A9)"}]
["y-intercept = b0 =",{"$f":"INTERCEPT(B4:B9,A4:A9)","v":-20406.279159504687}," =INTERCEPT(B4:B9,A4:A9)"]
["Estimated Regression Equation:",{"$f":"\"Yhat = \"&ROUND(B30,2)&\"*x + \"&ROUND(B31,2)","v":"Yhat = 8.44*x + -20406.28"},{"$f":"\" \"&IF(_xlfn.ISFORMULA(B34),_xlfn.FORMULATEXT(B34),\"\")","v":" =\"Yhat = \"&ROUND(B30,2)&\"*x + \"&ROUND(B31,2)"}]
["Min X",{"$f":"MIN(A4:A9)","v":14000},{"$f":"\" \"&IF(_xlfn.ISFORMULA(B37),_xlfn.FORMULATEXT(B37),\"\")","v":" "}]
["Max X",{"$f":"MAX(A4:A9)","v":43000},{"$f":"\" \"&IF(_xlfn.ISFORMULA(B35),_xlfn.FORMULATEXT(B35),\"\")","v":" =MIN(A4:A9)"}]
["x",40000,{"$f":"\" \"&IF(_xlfn.ISFORMULA(B36),_xlfn.FORMULATEXT(B36),\"\")","v":" =MAX(A4:A9)"}]
[{"$f":"\"Yhat when x = \"&ROUND(B37,0)&\" =\"","v":"Yhat when x = 40000 ="},{"$f":"B30*B37+B31","v":317029.43838355492},{"$f":"\" \"&IF(_xlfn.ISFORMULA(B38),_xlfn.FORMULATEXT(B38),\"\")","v":" =B30*B37+B31"}]
[]
["We can use equation to make predictions for a particular situation, or to estimate E(y) over the"]
["range of x values defined by the minimum and maximum x values from our sample."]

{"sheet":"C. of Determination r^2","index":4,"n_rows":43,"n_cols":6,"rows_kept":43,"cut":false,"hidden":false,"merged":[]}
["Ad $ Spent per Week and Weekly Sales"]
[null,null,null,null,"Explained","Unexplained"]
["Ad Dollars Spent per Week - Xi","Weekly Sales - Yi","Predicted Value = Yhat","(Yi - Ybar)^2","(Yhat - Ybar)^2","(Yi - Yhat)^2"]
[14000,97000,{"$f":"$B$31*A4+$B$32","v":97696.221980566173},{"$f":"(B4-$B$30)^2","v":16512250000},{"$f":"(C4-$B$30)^2","v":16333805676.040716},{"$f":"(B4-C4)^2","v":484725.0462234852}]
[27000,185000,{"$f":"$B$31*A5+$B$32","v":207362.83018206054},1640250000,328956929.00477409,{"$f":"(B5-C5)^2","v":500096173.75167787}]
[39900,260000,316185.84908969729,1190250000,8223923225.1193514,3156849637.9302382]
[17000,143000,123003.90079629564,6806250000,10505450351.975605,399843983.3643862]
[34000,270000,266414.08075209596,1980250000,1673962003.7890294,12858816.852488644]
[43000,398000,342337.11719928437,29756250000,13650911955.439312,3098356521.6862035]
[null,null,null,{"$f":"SUM(D4:D9)","v":57885500000},{"$f":"SUM(E4:E9)","v":50717010141.36879},{"$f":"SUM(F4:F9)","v":7168489858.631218}]
[null,null,null,"SST","SSR","SSE"]
[]
["Xbar","Y","X","Ybar"]
[{"$f":"$B$29","v":29150},{"$f":"MIN(B4:B9)","v":97000},{"$f":"MIN(A4:A9)","v":14000},{"$f":"$B$30","v":225500}]
[{"$f":"$B$29","v":29150},{"$f":"MAX(B4:B9)","v":398000},{"$f":"MAX(A4:A9)","v":43000},{"$f":"$B$30","v":225500}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Count = n =",{"$f":"COUNT(A4:A9)","v":6},{"$f":"\" \"&IF(_xlfn.ISFORMULA(B27),_xlfn.FORMULATEXT(B27),\"\")","v":" =COUNT(A4:A9)"}]
["n-1",{"$f":"B27-1","v":5}," =B27-1"]
["Xbar",{"$f":"AVERAGE(A4:A9)","v":29150}," =AVERAGE(A4:A9)"]
["Ybar",{"$f":"AVERAGE(B4:B9)","v":225500}," =AVERAGE(B4:B9)"]
["Slope = b1 =",{"$f":"SLOPE(B4:B9,A4:A9)","v":8.4358929385764903}," =SLOPE(B4:B9,A4:A9)"]
["y-intercept = b0 =",{"$f":"INTERCEPT(B4:B9,A4:A9)","v":-20406.279159504687}," =INTERCEPT(B4:B9,A4:A9)"]
["SST",{"$f":"D10","v":57885500000}," =D10","SST = ∑(Yi-Ybar)^2 = Total Sum of Squares. How well observations cluster around Ybar Line. Total Variation between Particular Y and Ybar."]
["SSR",{"$f":"E10","v":50717010141.36879}," =E10","SSR = ∑(Yhat-Ybar)^2 = Sum of Squares due to Regression. Variation from Predicted Y to Ybar. Explained part of SST."]
["SSE",{"$f":"F10","v":7168489858.631218}," =F10","SSE = ∑(Yi-Yhat)^2 = Sum of Squares due to Error/Residuals. How well observations cluster around Yhat Line. Unexplained part of SST."]
["r^2 = SSR/SST",{"$f":"B34/B33","v":0.87616087174454382}," =B34/B33","r^2 = Coefficient of Determination = Goodness of fit of Estimated Equation/Line to Sample Raw Data."]
["r^2 =",{"$f":"RSQ(B4:B9,A4:A9)","v":0.87616087174454393}," =RSQ(B4:B9,A4:A9)",null,"r^2 = % of SST explained by Estimated Regression Equation.  % of variability in y explained by the linear relationship between x & y."]
["r^2 = 1  - SSE/SST",{"$f":"1-B35/B33","v":0.87616087174454371}," =1-B35/B33",null,"r^2 = Measures strength and goodness of fit. 0 to 1. Works with Linear & nonlinear relationships and for 1 or more independent variables."]
["r = (Sign of b1)*SQRT(r^2)",{"$f":"SIGN(B31)*SQRT(B38)","v":0.93603465306822042}," =SIGN(B31)*SQRT(B38)",null,"r = Coefficient of Correlation = -1 to 1. Measures strength and direction of liner relationship with 1 independent variable."]
["r =",{"$f":"PEARSON(B4:B9,A4:A9)","v":0.93603465306822031},{"$f":"\" \"&IF(_xlfn.ISFORMULA(B40),_xlfn.FORMULATEXT(B40),\"\")","v":" =PEARSON(B4:B9,A4:A9)"}]
["This is a very good fit: about 87.6% of the variation in y can be explained by the Regression Equation/Line."]
["We can think of it this way: if we had not collected x and y data, we would be stuck with the Y = Ybar Line with the total Variation of (Y - Ybar). "]
["Our predictions would be much less accurate. Our Equation explains 87.5% of the variation above the Ybar Line."]

{"sheet":"Test Significance","index":5,"n_rows":150,"n_cols":22,"rows_kept":150,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Error"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Residual","Unexplianed portion of SST","Explained Portion of SST"]
[null,null,null,null,null,null,null,null,null,null,"Xi","Yi","(Xi - Xbar)","(Yi - Ybar)",{"$f":"M3&\"*\"&CHAR(10)&N3","v":"(Xi - Xbar)*\n(Yi - Ybar)"},{"$f":"M3&\"^2\"","v":"(Xi - Xbar)^2"},{"$f":"N3&\"^2\"","v":"(Yi - Ybar)^2"},"Predicted Value\n= Yhat","Predicted Value\n= Yhat","Residuals:\nYi - Yhat","Residuals\nSquared","Predicted - Ybar\nSquared"]
[null,null,null,null,null,null,null,null,null,null,14000,97000,{"$f":"K4-$K$13","v":-15150},{"$f":"L4-$K$14","v":-128500},{"$f":"N4*M4","v":1946775000},{"$f":"M4^2","v":229522500},16512250000,{"$f":"$K$15*K4+$K$17","v":97696.221980566173},{"$f":"FORECAST(K4,$L$4:$L$9,$K$4:$K$9)","v":97696.221980566173},{"$f":"L4-R4","v":-696.22198056617344},{"$f":"T4^2","v":484725.0462234852},{"$f":"(R4-$K$14)^2","v":16333805676.040716}]
[null,null,null,null,null,null,null,null,null,null,27000,185000,{"$f":"K5-$K$13","v":-2150},{"$f":"L5-$K$14","v":-40500},{"$f":"N5*M5","v":87075000},{"$f":"M5^2","v":4622500},{"$f":"N5^2","v":1640250000},{"$f":"$K$15*K5+$K$17","v":207362.83018206054},{"$f":"FORECAST(K5,$L$4:$L$9,$K$4:$K$9)","v":207362.83018206054},{"$f":"L5-R5","v":-22362.830182060541},{"$f":"T5^2","v":500096173.75167787},{"$f":"(R5-$K$14)^2","v":328956929.00477409}]
[null,null,null,null,null,null,null,null,null,null,39900,260000,10750,34500,370875000,115562500,1190250000,316185.84908969729,316185.84908969729,-56185.849089697294,{"$f":"T6^2","v":3156849637.9302382},{"$f":"(R6-$K$14)^2","v":8223923225.1193514}]
[null,null,null,null,null,null,null,null,null,null,17000,143000,-12150,-82500,1002375000,147622500,6806250000,123003.90079629564,123003.90079629564,19996.099203704362,399843983.3643862,10505450351.975605]
[null,null,null,null,null,null,null,null,null,null,34000,270000,4850,44500,215825000,23522500,1980250000,266414.08075209596,266414.08075209596,3585.9192479040357,12858816.852488644,1673962003.7890294]
[null,null,null,null,null,null,null,null,null,null,43000,398000,13850,172500,2389125000,191822500,29756250000,342337.11719928437,342337.11719928437,55662.882800715626,3098356521.6862035,13650911955.439312]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(O4:O9)","v":6012050000},{"$f":"SUM(P4:P9)","v":712675000},{"$f":"SUM(Q4:Q9)","v":57885500000},null,null,null,{"$f":"SUM(U4:U9)","v":7168489858.631218},{"$f":"SUM(V4:V9)","v":50717010141.36879}]
[null,null,null,null,null,null,null,null,null,"Count = n =",{"$f":"COUNT(K4:K9)","v":6},{"$f":"IF(_xlfn.ISFORMULA(K11),_xlfn.FORMULATEXT(K11),\"\")","v":"=COUNT(K4:K9)"},null,null,null,"(Xi - Xbar)^2","SST = ∑(Yi-Ybar)^2 = How well observations cluster around Ybar Line.",null,null,null,"SSE = ∑(Yi-Yhat)^2 = How well observations cluster around Yhat Line."]
[null,null,null,null,null,null,null,null,null,"n - 1 =",{"$f":"K11-1","v":5},{"$f":"IF(_xlfn.ISFORMULA(K12),_xlfn.FORMULATEXT(K12),\"\")","v":"=K11-1"},null,null,null,null,"Total Variation",null,null,null,null,"SSR = ∑(Yhat-Ybar)^2 = Sum of Squares of distances from Regression Line to Ybar Line (the distances that the Regression Line explains of the SST distance)."]
[null,null,null,null,null,null,null,null,null,"Xbar",{"$f":"AVERAGE(K4:K9)","v":29150},"=AVERAGE(K4:K9)"]
[null,null,null,null,null,null,null,null,null,"Ybar",{"$f":"AVERAGE(L4:L9)","v":225500},"=AVERAGE(L4:L9)"]
[null,null,null,null,null,null,null,null,null,"Slope = m = b1 =",{"$f":"O10/P10","v":8.4358929385764903},"=O10/P10",null,"b1 = ∑((Xi-Xbar)*(Yi-Ybar))/∑((Xi-Xbar)^2)"]
[null,null,null,null,null,null,null,null,null,"Slope = m = b1 =",{"$f":"SLOPE(L4:L9,K4:K9)","v":8.4358929385764903},"=SLOPE(L4:L9,K4:K9)"]
[null,null,null,null,null,null,null,null,null,"Intercept = b = b0 =",{"$f":"K14-K13*K15","v":-20406.279159504687},"=K14-K13*K15",null,"b0 = Ybar-Xbar*b1",null,null,null,null,null,null,"Section 12.5:"]
[null,null,null,null,null,null,null,null,null,"Intercept = b = b0 =",{"$f":"INTERCEPT(L4:L9,K4:K9)","v":-20406.279159504687},"=INTERCEPT(L4:L9,K4:K9)",null,null,null,null,null,null,null,null,"F Test Notes:"]
[null,null,null,null,null,null,null,null,null,"x",4,{"$f":"IF(_xlfn.ISFORMULA(K19),_xlfn.FORMULATEXT(K19),\"\")","v":""},null,null,null,null,null,null,null,null,"Method 1 to estimate Variantion for Error or y","Method 2 to estimate Variantion for Error or y"]
[null,null,null,null,null,null,null,null,null,{"$f":"\"Yhat = f(x) = Predicted Value = \"&SLOPE(L4:L9,K4:K9)&\" * x + \"&ROUND(INTERCEPT(L4:L9,K4:K9),1)","v":"Yhat = f(x) = Predicted Value = 8.43589293857649 * x + -20406.3"},{"$f":"K15*K19+K18","v":-20372.535587750383},{"$f":"IF(_xlfn.ISFORMULA(K20),_xlfn.FORMULATEXT(K20),\"\")","v":"=K15*K19+K18"}]
[]
[null,null,null,null,null,null,null,null,null,"SST = ∑(Yi-Ybar)^2",{"$f":"Q10","v":57885500000},{"$f":"IF(_xlfn.ISFORMULA(K22),_xlfn.FORMULATEXT(K22),\"\")","v":"=Q10"},null,"SST = ∑(Yi-Ybar)^2 = How well observations cluster around Ybar Line."]
[null,null,null,null,null,null,null,null,null,"SST = ∑(Yi-Ybar)^2",{"$f":"K24+K26","v":57885500000.000008},"=K24+K26"]
[null,null,null,null,null,null,null,null,null,"SSE = ∑(Yi-Yhat)^2",{"$f":"U10","v":7168489858.631218},"=U10",null,"SSE = ∑(Yi-Yhat)^2 = How well observations cluster around Yhat Line."]
[null,null,null,null,null,null,null,null,null,"SSE = ∑(Yi-Yhat)^2",{"$f":"SUMPRODUCT((L4:L9-FORECAST(K4:K9,L4:L9,K4:K9))^2)","v":7168489858.631218},"=SUMPRODUCT((L4:L9-FORECAST(K4:K9,L4:L9,K4:K9))^2)"]
[null,null,null,null,null,null,null,null,null,"SSR = ∑(Yhat-Ybar)^2",{"$f":"V10","v":50717010141.36879},"=V10",null,"SSR = ∑(Yhat-Ybar)^2 = Sum of Squares of distances from Regression Line to Ybar Line (the distances that the Regression Line explains of the SST distance)."]
[null,null,null,null,null,null,null,null,null,"SSR = ∑(Yhat-Ybar)^2",{"$f":"SUMPRODUCT((FORECAST(K4:K9,L4:L9,K4:K9)-K14)^2)","v":50717010141.36879},"=SUMPRODUCT((FORECAST(K4:K9,L4:L9,K4:K9)-K14)^2)"]
[null,null,null,null,null,null,null,null,null,"r^2 = Goodness of Fit = SSR/SST",{"$f":"K26/K22","v":0.87616087174454382},"=K26/K22",null,"r^2 = SSR/SST = Goodness of Fit of Regression Equation/Line to Raw Data = Proportion of Total Variation (SST) that is Explained"]
[null,null,null,null,null,null,null,null,null,"r^2 = Goodness of Fit = SSR/SST",{"$f":"RSQ(L4:L9,K4:K9)","v":0.87616087174454393},"=RSQ(L4:L9,K4:K9)",null,"by using the Regression Equation/Line. This number will vary between 0 and 1. It measures strength of relationship and can be used for 1 or more independent variables."]
[null,null,null,null,null,null,null,null,null,"This is a very good fit: about 87.5% of the variation in y can be explained by the Regression Equation/Line."]
[null,null,null,null,null,null,null,null,null,"We can think of it this way: if we had not collected x and y data, we would be stuck with the Y = Ybar Line with the total Variation of (Y - Ybar). "]
[null,null,null,null,null,null,null,null,null,"Our predictions would be much less accurate. Our Equation explains 87.5% of the variation above the Ybar Line."]
[]
[null,null,null,null,null,null,null,null,null,"r^2 = Goodness of Fit = SSR/SST",{"$f":"SIGN(K15)*SQRT(K28)","v":0.93603465306822042},{"$f":"IF(_xlfn.ISFORMULA(K34),_xlfn.FORMULATEXT(K34),\"\")","v":"=SIGN(K15)*SQRT(K28)"},null,"r = Correlation Coefficient = Sign of Slope * SQRT(r^2) = Direction and Stregnth of Linear Relationship. Number between -1 and 1. Can be used for only one independent variable."]
[null,null,null,null,null,null,null,null,null,"r^2 = Goodness of Fit = SSR/SST",{"$f":"O10/K12/PRODUCT(_xlfn.STDEV.S(K4:K9),_xlfn.STDEV.S(L4:L9))","v":0.93603465306822053},"=O10/K12/PRODUCT(STDEV.S(K4:K9),STDEV.S(L4:L9))",null,"r = ∑((Xi-Xbar)*(Yi-Ybar))/(n-1)/PRODUCT(sx*sy)"]
[]
[null,null,null,null,null,null,null,null,"a","Assumed Regression Model with Population Parameters is: y = β0 + β1*X + є "]
[null,null,null,null,null,null,null,null,null,"Least Squares Method used to estimate the b0 and b1 (Intercept and Slope)"]
[null,null,null,null,null,null,null,null,null,"Estimated Regression Equation: Yhat = b1*X + b0 "]
[null,null,null,null,null,null,null,null,null,"Assumptions of Error term, є:"]
[null,null,null,null,null,null,null,null,null,"1) The error term is a random variable with Expected Value of Zero. E(є) = 0"]
[null,null,null,null,null,null,null,null,null,"Implication:  = β0 and β1x are constants, therefore: E( β0) =  β0 and E( β1) =  β1  "]
[null,null,null,null,null,null,null,null,null,"Thus for a given value of x, the expected value of y: E(y) = β1*X + β0 , which generate the values directly on the Regression Line."]
[null,null,null,null,null,null,null,null,null,"2) The variance of є (s^2) about the Regression Line is the same for all X values."]
[null,null,null,null,null,null,null,null,null,"Implication: the variance of the y values about Regression Line equals s^2 and is the same for all X values."]
[null,null,null,null,null,null,null,null,null,"3) Values of є are independent."]
[null,null,null,null,null,null,null,null,null,"Implication: the value of є for a particular x is not related to the value of є for any other particular x;"]
[null,null,null,null,null,null,null,null,null,"thus, the value of y for a particular x is not related to the value of y for any other particular x."]
[null,null,null,null,null,null,null,null,null,"4) є is a normally distributed random variable for all values of x."]
[null,null,null,null,null,null,null,null,null,"Implication: because y is a linear function of є , y is also a normally distributed random variable for all x values. "]
[]
[null,null,null,null,null,null,null,null,null,"Estimate of s^2 = s^2 = MSE = Variance of epsilon = є = Error in y values about Regression Equation/Line "]
[null,null,null,null,null,null,null,null,null,"s^2 = MSE = SSE/(n-2) = ∑(Residuals^2)/(n-2) = ∑(Yi-Yhat)^2/(n-2) = ∑(Yi-b1*X + b0)^2/(n-2)"]
[null,null,null,null,null,null,null,null,null,"Subtract 2 from n when dividing because 2 variables are estimated: β1 and β0 "]
[null,null,null,null,null,null,null,null,null,"MSE provides an unbiased estimator of s^2."]
[null,null,null,null,null,null,null,null,null,"s = SQRT(s^2) = Standard Error of the Estimate = Standard Deviation for each distributions."]
[]
[]
[null,null,null,null,null,null,null,null,null,"SSE = How well observations cluster around Yhat Line.",{"$f":"U10","v":7168489858.631218},{"$f":"IF(_xlfn.ISFORMULA(K59),_xlfn.FORMULATEXT(K59),\"\")","v":"=U10"},"SSE = ∑(Yi-Yhat)^2 = How well observations cluster around Yhat Line."]
[null,null,null,null,null,null,null,null,null,"n =",{"$f":"K11","v":6},"=K11"]
[null,null,null,null,null,null,null,null,null,"Number of variables estimated: β1 and β0 ",2,""]
[null,null,null,null,null,null,null,null,null,"degrees of freedom",{"$f":"K60-K61","v":4},"=K60-K61","Sample size - # variables estimated"]
[null,null,null,null,null,null,null,null,"a","MSE = s^2 = estimate of variation =",{"$f":"K59/K62","v":1792122464.6578045},"=K59/K62","s^2 = Mean Square Error = MSE = SSE/(n-2) = ∑(Residuals^2)/(n-2) = ∑(Yi-Yhat)^2/(n-2) = ∑(Yi-b1*X + b0)^2/(n-2) = "]
[null,null,null,null,null,null,null,null,null,null,null,null,"Variance of epsilon = є = Error in y values about Regression Equation/Line "]
[null,null,null,null,null,null,null,null,"b","s = SQRT(MSE) = Standard Error of the Estimate =",{"$f":"SQRT(K63)","v":42333.467430129138},"=SQRT(K63)","s = SQRT(s^2) = Standard Error of the Estimate = estimate of Standard Deviation for each distributions."]
[null,null,null,null,null,null,null,null,null,"Standard Error of the Estimate =",{"$f":"STEYX(L4:L9,K4:K9)","v":42333.467430129116},"=STEYX(L4:L9,K4:K9)","Standard Error of the predicted y-values for each x in the Regression Equation."]
[null,null,null,null,null,null,null,null,"c","sb1 = Estimated Standard Deviation of Slope\nEstimated Standard Error of Slope",{"$f":"K66/SQRT(P10)","v":1.5857622672264347},"=K66/SQRT(P10)","sb1 = s/SQRT(∑(xi-xbar)^2) = Estimated Standard Error of Slope = Amount of Standard Error of the Estimate for every 1 unit of SQRT(∑(xi-xbar)^2)"]
[null,null,null,null,null,null,null,null,"d for t","H0 : β1 = 0"]
[null,null,null,null,null,null,null,null,null,"H0 : β1 <> 0"]
[null,null,null,null,null,null,null,null,null,"Alpha =",0.05]
[null,null,null,null,null,null,null,null,null,"Slope = m = b1 =",{"$f":"SLOPE(L4:L9,K4:K9)","v":8.4358929385764903},"=SLOPE(L4:L9,K4:K9)"]
[null,null,null,null,null,null,null,null,null,"sb1 = Estimated Standard Deviation of Slope\nEstimated Standard Error of Slope",{"$f":"K67","v":1.5857622672264347},"=K67"]
[null,null,null,null,null,null,null,null,null,"Test Statistic = t = b1/sb1",{"$f":"K71/K72","v":5.3197715148886875},"=K71/K72","Test Statistic = t = b1/sb1 = Slope/Estimated Standard Error of Slope"]
[null,null,null,null,null,null,null,null,null,"t Critical Value =",{"$f":"_xlfn.T.INV(K70/2,K62)","v":-2.7764451051977934},"=T.INV(K70/2,K62)"]
[null,null,null,null,null,null,null,null,null,"t Critical Value =",{"$f":"-_xlfn.T.INV.2T(K70,K62)","v":-2.7764451051977934},"=-T.INV.2T(K70,K62)"]
[null,null,null,null,null,null,null,null,null,"p-value =",{"$f":"_xlfn.T.DIST(K73,K62,1)*2","v":1.9939935107946538},"=T.DIST(K73,K62,1)*2"]
[null,null,null,null,null,null,null,null,null,"p-value =",{"$f":"_xlfn.T.DIST.2T(ABS(K73),K62)","v":6.0064892053461622E-3},"=T.DIST.2T(ABS(K73),K62)"]
[]
[null,null,null,null,null,null,null,null,null,"Because our Test Statistic is opast our Critical Value and because our p-value is smaller than our alpha, we reject H0 and accept Ha."]
[null,null,null,null,null,null,null,null,null,"It is reasonable to assume that the slope is not zero."]
[null,null,null,null,null,null,null,null,null,"The significance test based on our sample evidence suugests that the slope is not zero."]
[null,null,null,null,null,null,null,null,null,"The sample evidence suggests that there is a significant relationship between x and y."]
[null,null,null,null,null,null,null,null,null,"The evidence is sufficient to conclude that a significant relationship exists between x and y."]
[null,null,null,null,null,null,null,null,null,"We do run the risk of a Type 1 Error (Innocent, but found guilty, H0 was TRUE, even though we rejected it."]
[null,null,null,null,null,null,null,null,null,"The sample evidence suggests that there is a significant relationship between x and y and therefore:"]
[null,null,null,null,null,null,null,null,null,"we can be confident in using the Estimated Regression Equation to make predictions over the range of max and min x values."]
[]
[null,null,null,null,null,null,null,null,"d for F","H0 : β1 = 0"]
[null,null,null,null,null,null,null,null,null,"H0 : β1 <> 0"]
[null,null,null,null,null,null,null,null,null,"Alpha =",0.05]
[null,null,null,null,null,null,null,null,null,"SSR = ∑(Yhat-Ybar)^2",{"$f":"V10","v":50717010141.36879},{"$f":"IF(_xlfn.ISFORMULA(K91),_xlfn.FORMULATEXT(K91),\"\")","v":"=V10"},"SSR = ∑(Yhat-Ybar)^2 = Sum of Squares of distances from Regression Line to Ybar Line"]
[null,null,null,null,null,null,null,null,null,"Regression Degrees of Freedom\nNumber of Independent Variables =",1,"","For this text, Regression Degrees of Freedom is always equal to Number of Independent Variables."]
[null,null,null,null,null,null,null,null,null,"MSR = Estimate #1 for s^2 = SSR/1 =",{"$f":"K91/K92","v":50717010141.36879},"=K91/K92","Independent Estimate #1 for s^2 = MSR = SSR/1"]
[null,null,null,null,null,null,null,null,null,"SSE = ∑(Yi-Yhat)^2",{"$f":"U10","v":7168489858.631218},"=U10","SSE = ∑(Yi-Yhat)^2 = = ∑(Yi-b1*X + b0)^2/(n-2) = How well observations cluster around Yhat Line."]
[null,null,null,null,null,null,null,null,null,"n =",{"$f":"K11","v":6},"=K11","Sample Size"]
[null,null,null,null,null,null,null,null,null,"Number of variables estimated: β1 and β0 ",2,"","Subtract 2 from n when dividing because 2 variables are estimated: β1 and β0 "]
[null,null,null,null,null,null,null,null,null,"degrees of freedom",{"$f":"K95-K96","v":4},"=K95-K96","Sample size - # variables estimated"]
[null,null,null,null,null,null,null,null,null,"MSE = s^2 = estimate of s^2 =",{"$f":"K94/K97","v":1792122464.6578045},{"$f":"IF(_xlfn.ISFORMULA(K98),_xlfn.FORMULATEXT(K98),\"\")","v":"=K94/K97"},"s^2 = Mean Square Error = MSE = SSE/(n-2) = Variance of epsilon = є = Error in y values about Regression Equation/Line "]
[null,null,null,null,null,null,null,null,null,"F Test Statistic = MSR/MSE = (SSR/1)/(SST/(n-2))",{"$f":"K93/K98","v":28.299968970621052},{"$f":"IF(_xlfn.ISFORMULA(K99),_xlfn.FORMULATEXT(K99),\"\")","v":"=K93/K98"},"F Test Statistic = MSR/MSE = 2 estimates of s^2 = Variance of y for each x value."]
[null,null,null,null,null,null,null,null,null,"F Critical Value =",{"$f":"_xlfn.F.INV.RT(K90,K92,K97)","v":7.708647422176786},{"$f":"IF(_xlfn.ISFORMULA(K100),_xlfn.FORMULATEXT(K100),\"\")","v":"=F.INV.RT(K90,K92,K97)"}]
[null,null,null,null,null,null,null,null,null,"p-value",{"$f":"_xlfn.F.DIST.RT(K99,K92,K97)","v":6.0064892053461726E-3},{"$f":"IF(_xlfn.ISFORMULA(K101),_xlfn.FORMULATEXT(K101),\"\")","v":"=F.DIST.RT(K99,K92,K97)"}]
[]
[null,null,null,null,null,null,null,null,null,"Because our Test Statistic is opast our Critical Value and because our p-value is smaller than our alpha, we reject H0 and accept Ha."]
[null,null,null,null,null,null,null,null,null,"It is reasonable to assume that the slope is not zero."]
[null,null,null,null,null,null,null,null,null,"The significance test based on our sample evidence suugests that the slope is not zero."]
[null,null,null,null,null,null,null,null,null,"The sample evidence suggests that there is a significant relationship between x and y."]
[null,null,null,null,null,null,null,null,null,"The evidence is sufficient to conclude that a significant relationship exists between x and y."]
[null,null,null,null,null,null,null,null,null,"We do run the risk of a Type 1 Error (Innocent, but found guilty, H0 was TRUE, even though we rejected it."]
[null,null,null,null,null,null,null,null,null,"The sample evidence suggests that there is a significant relationship between x and y and therefore:"]
[null,null,null,null,null,null,null,null,null,"we can be confident in using the Estimated Regression Equation to make predictions over the range of max and min x values."]
[]
[]
[null,null,null,null,null,null,null,null,null,"LINEST function output:"]
[null,null,null,null,null,null,null,null,null,"Slope",{"$f":"LINEST(L4:L9,K4:K9,1,1)","v":8.4358929385764885},-20406.279159504629,"Intercept"]
[null,null,null,null,null,null,null,null,null,"Standard Error Slope",1.5857622672264349,49350.126010295637,"Standard Error of Intercept"]
[null,null,null,null,null,null,null,null,null,"Coefficient of Determination",0.87616087174454382,42333.467430129123,"Standard Error of y"]
[null,null,null,null,null,null,null,null,null,"F Test Statistic",28.299968970621066,4,"df"]
[null,null,null,null,null,null,null,null,null,"SS Regression",50717010141.36879,7168489858.6312141,"SS Residual (Error)"]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,"SUMMARY OUTPUT"]
[]
[null,null,null,null,null,null,null,null,null,"Regression Statistics"]
[null,null,null,null,null,null,null,null,null,"Multiple R",0.93577544083806541]
[null,null,null,null,null,null,null,null,null,"R Square",0.87567567567567572]
[null,null,null,null,null,null,null,null,null,"Adjusted R Square",0.83423423423423426]
[null,null,null,null,null,null,null,null,null,"Standard Error",8.7559503577091338]
[null,null,null,null,null,null,null,null,null,"Observations",5]
[]
[null,null,null,null,null,null,null,null,null,"ANOVA"]
[null,null,null,null,null,null,null,null,null,null,"df","SS","MS","F","Significance F"]
[null,null,null,null,null,null,null,null,null,"Regression",1,1620,1620,21.130434782608688,1.9348926859301809E-2]
[null,null,null,null,null,null,null,null,null,"Residual",3,230.00000000000011,76.6666666666667]
[null,null,null,null,null,null,null,null,null,"Total",4,1850]
[]
[null,null,null,null,null,null,null,null,null,null,"Coefficients","Standard Error","t Stat","P-value","Lower 95%","Upper 95%","Lower 95.0%","Upper 95.0%"]
[null,null,null,null,null,null,null,null,null,"Intercept",68,8.1774305481838478,8.3155704715954251,3.6444696173405801E-3,41.975766365218178,94.024233634781822,41.975766365218178,94.024233634781822]
[null,null,null,null,null,null,null,null,null,"Xi",-2.9999999999999996,0.65263000691504081,-4.5967852660972399,1.9348926859301823E-2,-5.0769599542240531,-0.92304004577594645,-5.0769599542240531,-0.92304004577594645]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,"RESIDUAL OUTPUT"]
[]
[null,null,null,null,null,null,null,null,null,"Observation","Predicted Yi","Residuals"]
[null,null,null,null,null,null,null,null,null,1,59,-4]
[null,null,null,null,null,null,null,null,null,2,32.000000000000007,7.9999999999999929]
[null,null,null,null,null,null,null,null,null,3,50,5]
[null,null,null,null,null,null,null,null,null,4,8.0000000000000071,1.9999999999999929]
[null,null,null,null,null,null,null,null,null,5,26.000000000000007,-11.000000000000007]