CCE 170 - Exam #1 KEY

Fall Semester 2021

21. (excel) The A & B columns of the following spreadsheet contain a table of mileage data. Each row represents a particular vehicle. The weight of the vehicle is shown in the first column and the gas mileage of the vehicle is shown in the second column. The weight-mileage data pairs for each vehicle are shown as blue dots in the plot on the right.

The objective of the spreadsheet is to come up with the equation of a line that best fits the data values. This is accomplished using a least squares curve fitting technique. If we let x = weight and y = mileage, the equation of the line representing the best fit to the data can be written as follows:

where

n = number of data pairs

Click here to download a copy of the spreadsheet. Then answer the following questions.

Click here to download a copy of the solution.

Before answering the questions, please note that a set of names has already been assigned to certain cells and ranges in the spreadsheet. The first two columns in the table have been named x and y. Each of the cells in B29:B35 has also been assigned a name. Before continuing, move the cursor through each of these cells and familiarize yourself with the cell and range names. All equations you enter below should reference these names.

a. (1.5 pts) Enter a formula in cell B29 that counts the number of data values in the x (weight) column using the COUNT fuction.

b. (1.5 pts) Enter a formula in cell B30 that computes the sum of the x (weight) values.

c. (1.5 pts) Enter a formula in cell B31 that computes the sum of the y (mileage) values.

d. (3 pts) Enter a formula in cell B32 that computes the sum of the x*y values. In order to do this without creating a new column, you will need to create an array formula.

e. (3 pts) Enter a formula in cell B33 that computes the sum of the x^2 values. Once again, you will need to use an array formula.

f. (4 pts) Enter a formula in cell B34 for computing the a coefficient in the best fit linear equation. Use the equation for a shown above and on the spreadsheet. Remember to use cell names as you enter the formula.

g. (3 pts) Enter a formula in cell B35 for computing the b coefficient in the best fit linear equation.

h. (1.5 pts) Using the values of a & b you just computed, enter an equation in cells C6:C27 for the ybf values using the best fit linear equation on the weights listed in column A (the weights will be the x part of the equation). Hint: If you have done everything correctly, you should see a red line that passes through the middle of the data points in the plot.

(Upload instructions and links went here)