Nov 16, 2026 | 654 words | 7 min read
6.1.2. PCA: Linear Regression in Excel#
Data analysts and engineers often examine historical trends to understand system behavior and make predictions about the future. Between 1984 and 2015, the prevalence of personal computers in US households increased dramatically, from a rare commodity to an essential technology. This dataset captures the percentage of US households with at least one computer during this period.
Linear regression provides a mathematical model to describe this relationship over time. By fitting a line to historical data, you can quantify the rate of adoption and make predictions about household computer ownership at years not directly measured in your dataset. This activity uses both interpolation (predicting values within the range of historical data) and extrapolation (predicting values beyond the observed time period) to demonstrate the power and limits of regression models. You will also evaluate when your predictions are reliable and when they exceed the physical boundaries of the real-world system.
Required Files#
Download the assignment template and save it as 6_1_2_computer_username.xlsx.
Reminder
Rename your files with your Purdue username by replacing “template” in the filename. Then fill out any required information in the template, such as your name, email, and course details.
Instructions#
Linear regression models a dependent variable based on independent variables. Engineers use linear regression to identify relationships, measure changes over time, and make predictions from data.
Review PCMs: Linear Regression in Excel as needed.
Part A: Model Parameters and Predictions#
In Table 2: Model Parameters, use Excel built-in functions to calculate three values for the dataset in Table 1:
Slope: Round to two decimal places.
Y-intercept: Round to an integer.
Coefficient of determination (\(R^2\)): Round to four decimal places.
Include units for all three values.
In Table 3: Predictions, use your calculated slope and y-intercept to predict household computer ownership percentages for the provided years using the linear model \(y = ax + b\). In the “Safe to Use” column, enter “Yes” or “No” to indicate whether the prediction falls within the reliable range based on interpolation, extrapolation, and physical constraints.
Part B: Scatter Plot#
Highlight your data columns in Table 1 and insert a 2D scatter chart.
Right-click on a data point on the generated chart and select Add Trendline.
Select the Linear option from the trendline menu.
Scroll down and check the boxes for Display Equation on chart and Display R-squared value on chart. Ensure the labels do not cover the trendline or data points.
Format the chart for a technical presentation:
Title: Percentage of US Households with a Computer 1984-2015
X-axis label: Time (years)
Y-axis label: Households (%)
Gradescope Submission#
Save Deliverables: Ensure all required assignment files are saved and named exactly as specified in the assignment instructions. If a template file name contains the word
template, replace only that word with your Purdue username. When a task lists more than one deliverable, such as a workbook and a report, submit each deliverable to its own matching Gradescope assignment instead of combining them into one upload.Navigate to Gradescope: Log in to Gradescope. From your Dashboard, open the assignment.
Upload your work: Select the Upload submission method. Drag and drop all required files (or a compressed
.zipfolder) into Gradescope, and select Upload.Confirm Submission: When your upload is successful, you will see a confirmation message on your screen and receive an automated email receipt.
Add Team Members (if applicable): If this is a team assignment, add all team members to the submission. Each teammate should confirm they can see the submission in their own Gradescope account. For help, visit Gradescope’s documentation on adding team members.
Review Autograder Results: Wait a few minutes for the autograder to evaluate the relevant files. Scroll down to view any failed test cases. If you need assistance interpreting the output or errors, ask your teaching team.
Resubmit if Necessary: You can edit your files and select Resubmit as many times as you need before the assignment deadline. Only your final, active submission is graded. (Note: If you need to resubmit, you must upload all required files for the assignment again).
For a detailed walkthrough, review the official Gradescope Submission Guide.