Www.betsymccall.net



MAT 135, Excel Lab #5, Spring 2016Name _________________________________Instructions: These Excel labs are designed to introduce you to basic statistical functions in Excel. Use the online resources linked in the lab, and the dataset (if one is provided), to perform the tasks requested in the lab. Labs can be submitted electronically by email, through Canvas, or as a printout (preferably color for labs that contain graphs; black-and-white okay, or primarily text output labs). Due dates for each lab are listed on the course syllabus. You can get assistance with Excel in the Math computer lab (such as in CRSC 190), or from me during office hours.Warning: Different versions of Excel have features in slightly different locations, particularly for graphing features. If the version of Excel in the links below don’t quite match what your version looks like, you can search for Excel help pages by version. Type the name of the feature, Excel, and the year your version of Office was released into a search engine.Resources: File: 135data5.xlsxPart 1: Add a Trendline to a ScatterplotRecreate your scatterplot from the last lab. Following the directions at the first weblink, add a trendline. Paste your graph below.Part 2: Exploring Linear Regression (Least-Squares Lines)Go to the second weblink above. Note the original location of the red dot, along with the correlation of the current configuration. Record the values below (4)Move the red dot to a new location. Note the new location and the new correlation. Record the values below (5). How did moving the point change the correlation?Move the red dot around to try to maximize the correlation value (6). How does moving the point closer to the other points, or further from it affect the correlation value?Can you move the single point to a location that makes the correlation negative? (7) How is this effect related to outliers on a scatterplot?Click on the arrow next to “Points”. Experiment with a different configuration of points on the graph and then repeat 1-4 above. Take a screen shot of your points and configurations and post them below. (8)Part 3: Exploring Least Squares Regression with PhETGo to the website the third weblink in the list at the top of the page. Click on the Play button for the simulation. Select Manatee Mortality vs. Time from the drop down menu in the center top of the screen.Adjust the values of a and b under My Line until you get a line that looks like a good fit for the data. Record the equation of the line below (9). Check the boxes for the residuals and square residuals. Use the sum bar to try to make your sum of squares as small as possible.(10)Click on the + next to the Best Fit Line. Record the value below (11). What is that equation and how does it compare to your estimates above?Compare square residuals as you adjust your line now (12). What happens to the squares on the graph as you change the line?Click on the + next to the Correlation Coefficient. (13) Analysis: Use you graphs to answer the following questions.Paste your scatterplot with trendline here.Does the trendline look like an appropriate model of the data aside from some random noise?Experiment on your graph with other types of trendlines. Can you get the r2/R2 value to increase above what it was on the linear graph? If so, does this line seem like a better fit? Does your answer change if the goal is to get a good fit with the simplest equation possible?For Part 2: record the starting coordinate of the red dot here. What is the second location? How did moving the point change the correlation?Maximum correlation cooridates? How does moving the point closer to the other points, or further from it affect the correlation value?What are the coordinates of the red dot that make the correlation negative? How is this effect related to outliers on a scatterplot?Take a screen shot of your points and configurations and post them here after adjusting original points.Part 3 starts here: What is the equation of your manual best-fit line?After turning on the residual squares, what line did you get now?What is that equation and how does it compare to your estimates above?What happens to the squares on the graph as you change the line?What is the value of the correlation coefficient?Interpret the result. How good is the fit?What proportion of the data can be explained by the regression relation?Interpret the slope and y-intercept in the context of the problem.If the pattern holds, how many manatee deaths are there likely to be this year (2016)? ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download