{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Main Sheet","Calculations (DO NOT EDIT!)","Author and Copyright"],"not_rendered":{"charts":1,"images":0,"macros":1,"external_links":0,"defined_names":1,"comments":0,"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":"Main Sheet","index":0,"n_rows":46,"n_cols":13,"rows_kept":46,"cut":false,"hidden":false,"merged":[]}
["This spreadsheet is designed to allow students to explore a line of best fit by directly experimenting with the slope and y-intercept to find the line that minimizes (or comes close to minimizing) the sum of the squared residuals."]
["Be sure to ONLY enter data in either the yellow or blue cells!"]
[]
["Step 1. Enter the (x,y) coordinates of the data in the yellow cells. As the points are entered, they should appear on the graph."]
["This spreadsheet allows up to 10 points. "]
[]
["Point 1:",null,"(",1,",",0,")",null,"For what it's worth: if the y-coordinates are integers, then the "]
[]
["Point 2:",null,"(",2,",",10,")",null,"slope and y-intercept of the line of best fit will have at most "]
[]
["Point 3:",null,"(",3,",",5,")",null,"one digit after the decimal point if either:"]
[]
["Point 4:",null,"(",4,",",20,")",null,"1.","There are four points, and the x-coordinates are"]
[]
["Point 5:",null,"(",null,",",null,")",null,null,"four consecutive integers, or"]
[]
["Point 6:",null,"(",null,",",null,")",null,"2.","There are five points, and the x-coordinates are"]
[]
["Point 7:",null,"(",null,",",null,")",null,null,"five consecutive integers, or"]
[]
["Point 8:",null,"(",null,",",null,")",null,"3.","There are four points, and the x-coordinates are"]
[]
["Point 9:",null,"(",null,",",null,")",null,null,"chosen by taking five consecutive integers and then"]
[]
["Point 10:",null,"(",null,",",null,")",null,null,"removing the one in the middle."]
[]
["Step 2. Important! Click the Reset button ...                        ",null,null,null,null,null,null,"…","before attempting to find the line of best fit."]
[]
["Step 3. Enter your guess for the slope and y-intercept in the blue spaces below. "]
["For a negative y-intercept, enter a negative number in cell L37. Enter 0 for a y-intercept of 0."]
["The graph of this line is shown in red to the right. "]
["The graph also shows the residuals, or the vertical distance of the data points to the line."]
["The spreadsheet automatically calculates the sum of the squared residuals based on the values entered in the blue cells."]
["Lines 39 and 40 show the line attempted thus far that has the smallest sum of the squared residuals."]
["The user should attempt different slopes and y-intercepts to make the sum of the squared residuals as small as possible."]
[]
["With this line…",null,null,null,null,null,null,"y = ",5,"x","+",-3]
["The sum of the squared residuals is…",null,null,null,null,null,null,{"$f":"'Calculations (DO NOT EDIT!)'!C10","v":71}]
[]
["Here is the best line found so far…",null,null,null,null,null,null,"y = ",null,"x","+"]
["Its sum of the squared residuals is…",null,null,null,null,null,null,1E+20]
[]
["Step 4. After you've tried out a few different lines, click the Answer button…",null,null,null,null,null,null,null,null,null,null,null,"… to reveal the actual line of best fit. Hopefully you were able to come close by guessing and checking!"]
[]
["This is the actual line of best fit…",null,null,null,null,null,null,"y = ",null,"x"]
["The minimum sum of the squared residuals is…"]

{"sheet":"Calculations (DO NOT EDIT!)","index":1,"n_rows":52,"n_cols":27,"rows_kept":52,"cut":false,"hidden":false,"merged":[[6,0,6,2],[48,21,48,23],[3,21,3,23],[8,21,8,23],[13,21,13,23],[18,21,18,23],[23,21,23,23],[28,21,28,23],[33,21,33,23],[38,21,38,23],[43,21,43,23]]}
[null,null,null,null,"+"]
[null,null,null,null,"-"]
[]
[null,null,null,null,null,null,"X",{"$f":"IF('Main Sheet'!D7=\"\",\"\",VALUE('Main Sheet'!D7))","v":1},{"$f":"IF('Main Sheet'!D9=\"\",\"\",VALUE('Main Sheet'!D9))","v":2},{"$f":"IF('Main Sheet'!D11=\"\",\"\",VALUE('Main Sheet'!D11))","v":3},{"$f":"IF('Main Sheet'!D13=\"\",\"\",VALUE('Main Sheet'!D13))","v":4},{"$f":"IF(OR('Main Sheet'!D15=\"\",'Main Sheet'!F15=\"\"),\"\",VALUE('Main Sheet'!D15))","v":""},{"$f":"IF(OR('Main Sheet'!D17=\"\",'Main Sheet'!F17=\"\"),\"\",VALUE('Main Sheet'!D17))","v":""},{"$f":"IF(OR('Main Sheet'!D19=\"\",'Main Sheet'!F19=\"\"),\"\",VALUE('Main Sheet'!D19))","v":""},{"$f":"IF(OR('Main Sheet'!D21=\"\",'Main Sheet'!F21=\"\"),\"\",VALUE('Main Sheet'!D21))","v":""},{"$f":"IF(OR('Main Sheet'!D23=\"\",'Main Sheet'!F23=\"\"),\"\",VALUE('Main Sheet'!D23))","v":""},{"$f":"IF(OR('Main Sheet'!D25=\"\",'Main Sheet'!F25=\"\"),\"\",VALUE('Main Sheet'!D25))","v":""},null,{"$f":"MIN(H4:Q4)","v":1},{"$f":"MAX(H4:Q4)","v":4},null,"Residual 1",null,null,null,{"$f":"H4","v":1},{"$f":"H5","v":0}]
[null,null,null,null,null,null,"Y",{"$f":"IF('Main Sheet'!F7=\"\",\"\",'Main Sheet'!F7)","v":0},{"$f":"IF('Main Sheet'!F9=\"\",\"\",'Main Sheet'!F9)","v":10},{"$f":"IF('Main Sheet'!F11=\"\",\"\",'Main Sheet'!F11)","v":5},{"$f":"IF('Main Sheet'!F13=\"\",\"\",'Main Sheet'!F13)","v":20},{"$f":"IF(OR('Main Sheet'!D15=\"\",'Main Sheet'!F15=\"\"),\"\",'Main Sheet'!F15)","v":""},{"$f":"IF(OR('Main Sheet'!D17=\"\",'Main Sheet'!F17=\"\"),\"\",'Main Sheet'!F17)","v":""},{"$f":"IF(OR('Main Sheet'!D19=\"\",'Main Sheet'!F19=\"\"),\"\",'Main Sheet'!F19)","v":""},{"$f":"IF(OR('Main Sheet'!D21=\"\",'Main Sheet'!F21=\"\"),\"\",'Main Sheet'!F21)","v":""},{"$f":"IF(OR('Main Sheet'!D23=\"\",'Main Sheet'!F23=\"\"),\"\",'Main Sheet'!F23)","v":""},{"$f":"IF(OR('Main Sheet'!D25=\"\",'Main Sheet'!F25=\"\"),\"\",'Main Sheet'!F25)","v":""},null,null,null,null,{"$f":"IF(H4=\"\",\"\",H4)","v":1},{"$f":"IF(H4=\"\",\"\",H5)","v":0},null,null,{"$f":"IF(I4=\"\",H4,I4)","v":2},{"$f":"I5","v":10}]
[null,null,null,null,null,null,"Y predict",{"$f":"IF(H4=\"\",\"\",IF('Main Sheet'!$K$37=\"+\",'Main Sheet'!$I$37*H4+'Main Sheet'!$L$37,'Main Sheet'!$I$37*H4-'Main Sheet'!$L$37))","v":2},{"$f":"IF(I4=\"\",\"\",IF('Main Sheet'!$K$37=\"+\",'Main Sheet'!$I$37*I4+'Main Sheet'!$L$37,'Main Sheet'!$I$37*I4-'Main Sheet'!$L$37))","v":7},{"$f":"IF(J4=\"\",\"\",IF('Main Sheet'!$K$37=\"+\",'Main Sheet'!$I$37*J4+'Main Sheet'!$L$37,'Main Sheet'!$I$37*J4-'Main Sheet'!$L$37))","v":12},{"$f":"IF(K4=\"\",\"\",IF('Main Sheet'!$K$37=\"+\",'Main Sheet'!$I$37*K4+'Main Sheet'!$L$37,'Main Sheet'!$I$37*K4-'Main Sheet'!$L$37))","v":17},{"$f":"IF(L4=\"\",\"\",IF('Main Sheet'!$K$37=\"+\",'Main Sheet'!$I$37*L4+'Main Sheet'!$L$37,'Main Sheet'!$I$37*L4-'Main Sheet'!$L$37))","v":""},{"$f":"IF(M4=\"\",\"\",IF('Main Sheet'!$K$37=\"+\",'Main Sheet'!$I$37*M4+'Main Sheet'!$L$37,'Main Sheet'!$I$37*M4-'Main Sheet'!$L$37))","v":""},{"$f":"IF(N4=\"\",\"\",IF('Main Sheet'!$K$37=\"+\",'Main Sheet'!$I$37*N4+'Main Sheet'!$L$37,'Main Sheet'!$I$37*N4-'Main Sheet'!$L$37))","v":""},{"$f":"IF(O4=\"\",\"\",IF('Main Sheet'!$K$37=\"+\",'Main Sheet'!$I$37*O4+'Main Sheet'!$L$37,'Main Sheet'!$I$37*O4-'Main Sheet'!$L$37))","v":""},{"$f":"IF(P4=\"\",\"\",IF('Main Sheet'!$K$37=\"+\",'Main Sheet'!$I$37*P4+'Main Sheet'!$L$37,'Main Sheet'!$I$37*P4-'Main Sheet'!$L$37))","v":""},{"$f":"IF(Q4=\"\",\"\",IF('Main Sheet'!$K$37=\"+\",'Main Sheet'!$I$37*Q4+'Main Sheet'!$L$37,'Main Sheet'!$I$37*Q4-'Main Sheet'!$L$37))","v":""},null,{"$f":"IF('Main Sheet'!I37>0,MIN(H6:Q6),MAX(H6:Q6))","v":2},{"$f":"IF('Main Sheet'!I37>0,MAX(H6:Q6),MIN(H6:Q6))","v":17},null,{"$f":"IF(H4=\"\",\"\",H4)","v":1},{"$f":"IF(H4=\"\",\"\",(W5+W7)/2)","v":1},{"$f":"IF(H4=\"\",\"\",H7)","v":-2},null,{"$f":"IF(J4=\"\",H4,J4)","v":3},{"$f":"J5","v":5}]
[" ",null,null,null,null,null,"Residual",{"$f":"IF(H4=\"\",\"\",IFERROR(H5-H6,\"\"))","v":-2},3,-7,3,"","","","",{"$f":"IF(P4=\"\",\"\",IFERROR(P5-P6,\"\"))","v":""},{"$f":"IF(Q4=\"\",\"\",IFERROR(Q5-Q6,\"\"))","v":""},null,null,null,null,{"$f":"IF(H4=\"\",\"\",H4)","v":1},{"$f":"IF(H4=\"\",\"\",H6)","v":2},null,null,{"$f":"IF(K4=\"\",H4,K4)","v":4},{"$f":"K5","v":20}]
["Slope",null,{"$f":"'Main Sheet'!I37","v":5},null,null,null,"Residual squared",{"$f":"IF(H4=\"\",\"\",IFERROR(H7^2,\"\"))","v":4},9,49,9,"","","","",{"$f":"IF(P4=\"\",\"\",IFERROR(P7^2,\"\"))","v":""},{"$f":"IF(Q4=\"\",\"\",IFERROR(Q7^2,\"\"))","v":""},null,null,null,null,null,null,null,null,{"$f":"IF(L4=\"\",H4,L4)","v":1},{"$f":"L5","v":""}]
["Intercept",null,{"$f":"'Main Sheet'!L37","v":-3},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Residual 2",null,null,null,{"$f":"IF(M4=\"\",H4,M4)","v":1},{"$f":"M5","v":""}]
["Sum of squared residuals",null,{"$f":"SUM(H8:Q8)","v":71},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(I4=\"\",\"\",I4)","v":2},{"$f":"IF(I4=\"\",\"\",I5)","v":10},null,null,{"$f":"IF(N4=\"\",H4,N4)","v":1},{"$f":"N5","v":""}]
[null,null,null,null,null,null,"X^2",{"$f":"IFERROR(H4^2,\"\")","v":1},{"$f":"IFERROR(I4^2,\"\")","v":4},9,16,"","","","","","",null,null,null,null,{"$f":"IF(I4=\"\",\"\",I4)","v":2},{"$f":"IF(I4=\"\",\"\",(W10+W12)/2)","v":8.5},{"$f":"IF(I4=\"\",\"\",I7)","v":3},null,{"$f":"IF(O4=\"\",H4,O4)","v":1},{"$f":"O5","v":""}]
[null,null,null,null,null,null,"XY",{"$f":"IFERROR(H4*H5,\"\")","v":0},{"$f":"IFERROR(I4*I5,\"\")","v":20},15,80,"","","","","","",null,null,null,null,{"$f":"IF(I4=\"\",\"\",I4)","v":2},{"$f":"IF(I4=\"\",\"\",I6)","v":7},null,null,{"$f":"IF(P4=\"\",H4,P4)","v":1},{"$f":"P5","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(Q4=\"\",H4,Q4)","v":1},{"$f":"Q5","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Residual 3"]
[null,null,null,null,null,null,"Sum X",{"$f":"SUM(H4:Q4)","v":10},null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(J4=\"\",\"\",J4)","v":3},{"$f":"IF(J4=\"\",\"\",J5)","v":5}]
[null,null,null,null,null,null,"Sum X^2",{"$f":"SUM(H11:Q11)","v":30},null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(J4=\"\",\"\",J4)","v":3},{"$f":"IF(J4=\"\",\"\",(W15+W17)/2)","v":8.5},{"$f":"IF(J4=\"\",\"\",J7)","v":-7}]
[null,null,null,null,null,null,"Sum Y",{"$f":"SUM(H5:Q5)","v":35},null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(J4=\"\",\"\",J4)","v":3},{"$f":"IF(J4=\"\",\"\",J6)","v":12}]
[null,null,null,null,null,null,"Sum X*Y",{"$f":"SUM(H12:Q12)","v":115}]
[null,null,null,null,null,null,"Count",{"$f":"COUNT(H4:Q4)","v":4},null,null,null,null,null,null,null,null,null,null,null,null,null,"Residual 4"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(K4=\"\",\"\",K4)","v":4},{"$f":"IF(K4=\"\",\"\",K5)","v":20}]
[null,null,null,null,null,null,"Slope",{"$f":"(H19*H18-H15*H17)/(H19*H16-H15^2)","v":5.5},null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(K4=\"\",\"\",K4)","v":4},{"$f":"IF(K4=\"\",\"\",(W20+W22)/2)","v":18.5},{"$f":"IF(K4=\"\",\"\",K7)","v":3}," "]
[null,null,null,null,null,null,"Intercept",{"$f":"(H17-H21*H15)/H19","v":-5},null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(K4=\"\",\"\",K4)","v":4},{"$f":"IF(K4=\"\",\"\",K6)","v":17}]
[null,null,null,null,null,null,null,{"$f":"IF(H22>0,\"+\",\"-\")","v":"-"}]
[null,null,null,null,null,null,null,{"$f":"ABS(H22)","v":5},null,null,null,null,null,null,null,null,null,null,null,null,null,"Residual 5"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(L4=\"\",\"\",L4)","v":""},{"$f":"IF(L4=\"\",\"\",L5)","v":""}]
[null,null,null,null,null,null,"X",{"$f":"H4","v":1},{"$f":"I4","v":2},3,4,"","","","","","",null,null,null,null,{"$f":"IF(L4=\"\",\"\",L4)","v":""},{"$f":"IF(L4=\"\",\"\",(W25+W27)/2)","v":""},{"$f":"IF(L4=\"\",\"\",L7)","v":""}]
[null,null,null,null,null," ","Y",{"$f":"H5","v":0},{"$f":"I5","v":10},5,20,"","","","","","",null,null,null,null,{"$f":"IF(L4=\"\",\"\",L4)","v":""},{"$f":"IF(L4=\"\",\"\",L6)","v":""}]
[null,null,null,null,null,null,"Y predict best",{"$f":"IF(H26=\"\",\"\",$H$21*H26+$H$22)","v":0.5},{"$f":"IF(I26=\"\",\"\",$H$21*I26+$H$22)","v":6},11.5,17,"","","","","",""]
[null,null,null,null,null,null,"Residual",{"$f":"IF(H26=\"\",\"\",IFERROR(H27-H28,\"\"))","v":-0.5},{"$f":"IF(I26=\"\",\"\",IFERROR(I27-I28,\"\"))","v":4},-6.5,3,"","","","","","",null,null,null,null,"Residual 6"]
[null,null,{"$f":"SUM(H30:Q30)","v":67.5},null,null,null,"Residual squared",{"$f":"IFERROR(H29^2,\"\")","v":0.25},{"$f":"IFERROR(I29^2,\"\")","v":16},42.25,9,"","","","","","",null,null,null,null,{"$f":"IF(M4=\"\",\"\",M4)","v":""},{"$f":"IF(M4=\"\",\"\",M5)","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(M4=\"\",\"\",M4)","v":""},{"$f":"IF(M4=\"\",\"\",(W30+W32)/2)","v":""},{"$f":"IF(M4=\"\",\"\",M7)","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(M4=\"\",\"\",M4)","v":""},{"$f":"IF(M4=\"\",\"\",M6)","v":""}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Residual 7"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(N4=\"\",\"\",N4)","v":""},{"$f":"IF(N4=\"\",\"\",N5)","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(N4=\"\",\"\",N4)","v":""},{"$f":"IF(N4=\"\",\"\",(W35+W37)/2)","v":""},{"$f":"IF(N4=\"\",\"\",N7)","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(N4=\"\",\"\",N4)","v":""},{"$f":"IF(N4=\"\",\"\",N6)","v":""}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Residual 8"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(O4=\"\",\"\",O4)","v":""},{"$f":"IF(O4=\"\",\"\",O5)","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(O4=\"\",\"\",O4)","v":""},{"$f":"IF(O4=\"\",\"\",(W40+W42)/2)","v":""},{"$f":"IF(O4=\"\",\"\",O7)","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(O4=\"\",\"\",O4)","v":""},{"$f":"IF(O4=\"\",\"\",O6)","v":""}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Residual 9"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(P4=\"\",\"\",P4)","v":""},{"$f":"IF(P4=\"\",\"\",P5)","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(P4=\"\",\"\",P4)","v":""},{"$f":"IF(P4=\"\",\"\",(W45+W47)/2)","v":""},{"$f":"IF(P4=\"\",\"\",P7)","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(P4=\"\",\"\",P4)","v":""},{"$f":"IF(P4=\"\",\"\",P6)","v":""}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Residual 10"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(Q4=\"\",\"\",Q4)","v":""},{"$f":"IF(Q4=\"\",\"\",Q5)","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(Q4=\"\",\"\",Q4)","v":""},{"$f":"IF(Q4=\"\",\"\",(W50+W52)/2)","v":""},{"$f":"IF(Q4=\"\",\"\",Q7)","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(Q4=\"\",\"\",Q4)","v":""},{"$f":"IF(Q4=\"\",\"\",Q6)","v":""}]

{"sheet":"Author and Copyright","index":2,"n_rows":25,"n_cols":1,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
["Exploring the Line of Best Fit"]
["© 2016 by John A. Quintanilla"]
[]
["This spreadsheet was created in conjunction with my paper"]
["J. Quintanilla, \"Deriving the Regression Line with Algebra,\""]
["Mathematics Teacher, Vol. 111, No. 8, pp. 594-598 (April 2017)."]
[]
["You may distribute this spreadsheet at no charge for"]
["educational purposes."]
[]
["Please send bugs, comments, and suggestions to"]
["John.Quintanilla@unt.edu"]
[]
["Contact Information"]
["Dr. John Quintanilla"]
["Department of Mathematics"]
["University of North Texas"]
["1155 Union Circle #311430"]
["Denton, Texas  76203-5017"]
[]
["Web page"]
["http://www.math.unt.edu/~johnq"]
[]
["Blog on mathematics and mathematics education"]
["http://www.meangreenmath.com"]