\[ \begin{align}\begin{aligned}\newcommand\blank{~\underline{\hspace{1.2cm}}~}\\% Bold symbols (vectors) \newcommand\bs[1]{\mathbf{#1}}\\% Differential \newcommand\dd[2][]{\mathrm{d}^{#1}{#2}} % use as \dd, \dd{x}, or \dd[2]{x}\\% Poor man's siunitx \newcommand\unit[1]{\mathrm{#1}} \newcommand\num[1]{#1} \newcommand\qty[2]{#1~\unit{#2}}\\\newcommand\per{/} \newcommand\squared{{}^2} \newcommand\cubed{{}^3} % % Scale \newcommand\milli{\unit{m}} \newcommand\centi{\unit{c}} \newcommand\kilo{\unit{k}} \newcommand\mega{\unit{M}} % % Percent \newcommand\percent{\unit{{\kern-4mu}\%}} % % Angle \newcommand\radian{\unit{rad}} \newcommand\degree{\unit{{\kern-4mu}^\circ}} % % Time \newcommand\second{\unit{s}} \newcommand\s{\second} \newcommand\minute{\unit{min}} \newcommand\hour{\unit{h}} % % Distance \newcommand\meter{\unit{m}} \newcommand\m{\meter} \newcommand\inch{\unit{in}} \newcommand\foot{\unit{ft}} % % Force \newcommand\newton{\unit{N}} \newcommand\kip{\unit{kip}} % kilopound in "freedom" units - edit made by Sri % % Mass \newcommand\gram{\unit{g}} \newcommand\g{\gram} \newcommand\kilogram{\unit{kg}} \newcommand\kg{\kilogram} \newcommand\grain{\unit{grain}} \newcommand\ounce{\unit{oz}} % % Temperature \newcommand\kelvin{\unit{K}} \newcommand\K{\kelvin} \newcommand\celsius{\unit{{}^\circ C}} \newcommand\C{\celsius} \newcommand\fahrenheit{\unit{{}^\circ F}} \newcommand\F{\fahrenheit} % % Area \newcommand\sqft{\unit{sq\,\foot}} % square foot % % Volume \newcommand\liter{\unit{L}} \newcommand\gallon{\unit{gal}} % % Frequency \newcommand\hertz{\unit{Hz}} \newcommand\rpm{\unit{rpm}} % % Voltage \newcommand\volt{\unit{V}} \newcommand\V{\volt} \newcommand\millivolt{\milli\volt} \newcommand\mV{\milli\volt} \newcommand\kilovolt{\kilo\volt} \newcommand\kV{\kilo\volt} % % Current \newcommand\ampere{\unit{A}} \newcommand\A{\ampere} \newcommand\milliampereA{\milli\ampere} \newcommand\mA{\milli\ampere} \newcommand\kiloampereA{\kilo\ampere} \newcommand\kA{\kilo\ampere} % % Resistance \newcommand\ohm{\Omega} \newcommand\milliohm{\milli\ohm} \newcommand\kiloohm{\kilo\ohm} % correct SI spelling \newcommand\kilohm{\kilo\ohm} % "American" spelling used in siunitx \newcommand\megaohm{\mega\ohm} % correct SI spelling \newcommand\megohm{\mega\ohm} % "American" spelling used in siunitx % % Capacitance \newcommand\farad{\unit{F}} \newcommand\F{\farad} \newcommand\microfarad{\micro\farad} \newcommand\muF{\micro\farad} % % Inductance \newcommand\henry{\unit{H}} \newcommand\H{\henry} \newcommand\millihenry{\milli\henry} \newcommand\mH{\milli\henry} % % Power \newcommand\watt{\unit{W}} \newcommand\W{\watt} \newcommand\milliwatt{\milli\watt} \newcommand\mW{\milli\watt} \newcommand\kilowatt{\kilo\watt} \newcommand\kW{\kilo\watt} % % Energy \newcommand\joule{\unit{J}} \newcommand\J{\joule} % % Composite units % % Torque \newcommand\ozin{\unit{\ounce}\,\unit{in}} \newcommand\newtonmeter{\unit{\newton\,\meter}} % % Pressure \newcommand\psf{\unit{psf}} % pounds per square foot \newcommand\pcf{\unit{pcf}} % pounds per cubic foot \newcommand\pascal{\unit{Pa}} \newcommand\Pa{\pascal} \newcommand\ksi{\unit{ksi}} % kilopound per square inch % NOTE: do NOT define \bar -- it is the built-in LaTeX accent used by \bar{x}, % \bar{y} (sample means). Redefining it made those render as the word "bar". \newcommand\barunit{\unit{bar}} \end{aligned}\end{align} \]

Nov 16, 2026 | 1623 words | 16 min read

6.1.1. PCMs: Linear Regression in Excel#

These pre-class materials introduce linear regression analysis, a method engineers use to model relationships between variables and make predictions based on data. They also explain how to use Excel to calculate a best-fit linear model for a dataset.

In engineering, modeling data with a best-fit line helps determine key system characteristics. For example, testing the displacement of a mechanical spring to determine its overall stiffness. Software tools like Excel are used to quickly evaluate data and extract system characteristics.

Purpose of Linear Regression#

Linear regression models a dependent variable based on one or more independent variables. Engineers use this analysis to:

  • Identify cause-and-effect relationships.

  • Measure temporal changes (changes over time).

  • Make predictions using existing data.

  • Find correlations within datasets.

Example Problem: Spring Constant Analysis#

A company is designing a new ballpoint pen, but the inkwell retraction mechanisms are failing quality checks. An engineer performs force and displacement tests on the springs. According to Hooke’s Law (\(F = kx\)), the force (\(F\)) required to stretch or compress a spring is linearly related to the displacement (\(x\)). The slope of this relationship is the spring constant (\(k\)), representing the stiffness of the spring.

Least-Squares Regression#

Least-squares regression is a mathematical method used to determine a best-fit line for a dataset. The resulting model takes the form of a linear equation:

(6.1)#\[f(x) = ax + b\]

Where:

  • \(x\) = independent variable

  • \(a\) = slope

  • \(b\) = y-intercept

The goal of least-squares regression is to find the slope (\(a\)) and y-intercept (\(b\)) values that minimize the sum of squared errors (SSE) between the model’s expected values and the actual collected data points.

Sum of Squared Errors (SSE)#

SSE measures how well a model matches the actual data. It is calculated by squaring the individual error terms and summing them together:

(6.2)#\[SSE = \sum^n_{i=1} [y_i - f(x_i)]^2\]

Where:

  • \(y_i\) = the actual, measured dependent variable data point

  • \(f(x_i)\) = the expected value predicted by the model line

The errors are the difference between the actual data and our best guess of a model. These errors have nothing to do with the accuracy or precision of the measurement of either of the variables or the collection of the data.

Once a best-fit line is calculated, engineers must determine how well the model actually fits the original data. In addition to SSE, there are several conventional statistical measures used to evaluate this fit.

Sum of Squares Total (SST)#

SST measures the variability in the actual data by comparing each measured data point to the mean of all measured y-values. It is calculated by squaring these differences and summing them together:

(6.3)#\[SST = \sum (y_i - \overline{y})^2\]

Where:

  • \(y_i\) = the actual, measured dependent variable data point

  • \(\overline{y}\) = the mean of all actual y-values

If SST is small, the data points do not deviate much from the mean. If SST is large, the data deviate a lot from the mean.

Coefficient of Determination (\(R^2\))#

The coefficient of determination, \(R^2\), measures the percentage of variability in the data that can be explained by the linear model. It is calculated using the relationship between the sum of squared errors (SSE) and the sum of squares total (SST):

(6.4)#\[R^2 = 1 - \frac{\text{SSE}}{\text{SST}}\]

The value of \(R^2\) always ranges from 0 to 1. A value closer to 1 indicates that the model line fits the data well. A small \(R^2\) value only means the model does not explain the variance; it does not necessarily indicate a small amount of error. SSE is the appropriate measure for the amount of error.

Correlation Coefficient (\(R\))#

The correlation coefficient, \(R\), measures the strength and direction of the linear relationship between the independent and dependent variables. It is calculated by taking the square root of \(R^2\).

The value of \(R\) ranges from -1 to 1. A value close to 0 means there is very little correlation between the variables, while the strength of the relationship increases as the value moves closer to either 1 or -1.

  • Positive \(R\): Indicates a direct relationship (positive slope). An increase in the independent variable (\(x\)) tends to result in an increase in the dependent variable (\(y\)).

  • Negative \(R\): Indicates an indirect relationship (negative slope). An increase in the independent variable (\(x\)) tends to result in a decrease in the dependent variable (\(y\)).

Check for Understanding

An engineer calculates the Sum of Squared Errors (SSE) for a linear model predicting agricultural drone flight times based on payload weight. What do the specific “errors” in this calculation represent?

A. The level of noise and inaccuracy present in the drone’s telemetry sensors.
B. The physical mistakes made by the engineering team when collecting the flight data.
C. The difference between the actual measured flight times and the expected flight times predicted by the model.
D. The percentage of total variation in the data that the linear model can successfully explain.

Show Answer

Correct Answer: C

  • The errors used to calculate SSE are strictly the mathematical difference between the measured data point and the model’s prediction.

  • These errors do not reflect the accuracy of the measuring instruments, noise in the data, or poor data collection practices.

  • The percentage of variation explained by the model is the coefficient of determination (\(R^2\)), not the SSE.

Check for Understanding

An engineer is evaluating a linear model and calculates an \(R^2\) value of 0.15. Which statement is true regarding the model’s error?

A. The model has a very small amount of error.
B. The \(R^2\) value does not indicate the specific amount of error in the model.
C. The model explains 85% of the variation, indicating a large error.
D. The model’s error is exactly 15%.

Show Answer

Correct Answer: B

  • \(R^2\) measures the percentage of variance explained by the model, not the amount of error.

  • To determine the amount of error in the model, the engineer must look at the Sum of Squared Errors (SSE).

  • An \(R^2\) of 0.15 means the model explains only 15% of the variance, not 85%.

Calculating Regression in Excel#

When evaluating bivariate data, you can use built-in Excel formulas or charting tools to quickly calculate the best-fit line and correlation values.

Excel Functions#

Model Parameters#

Excel requires the known y-values to be selected before the known x-values.

  1. Slope (\(m\)): To calculate the slope, use the syntax =SLOPE(known_y_values, known_x_values).

  2. Y-Intercept (\(b\)): To calculate the y-intercept, use the syntax =INTERCEPT(known_y_values, known_x_values).

Regression Diagnostics#

Once you have calculated your model parameters (slope and intercept), you must evaluate how well your model represents your raw data. Rather than performing tedious row-by-row manual calculations, you can use built-in Excel functions to quickly compute your regression diagnostics:=

  1. Mean Observed Value (\(\overline{y}\)):

    • Definition: The average of all actual dependent variable values in your sample.

    • Excel Syntax: =AVERAGE(known_y_values)

  2. Sum of Squares Total (SST):

    • Definition: Measures the total natural variability in your dependent variable: \(SST = \sum(y_i - \overline{y})^2\)

  • Excel Syntax: Instead of calculating \((y_i - \overline{y})^2\) for every row, Excel provides a high-efficiency function that computes the sum of squared deviations from the sample mean in a single step: =DEVSQ(known_y_values)

  1. Sum of Squared Errors (SSE):

    • Definition: Measures the unexplained error remaining between your actual data points and the model’s predictions: \(SSE = \sum [y_i - f(x_i)]^2\)

    • Excel Syntax: Excel does not have a single built-in function to find SSE directly from raw values. To find this in Excel, you must:

      • Create an intermediate column to calculate the individual squared error for each row: =(y_actual - y_predicted)^2 (where y_predicted is calculated using your model equation).

      • Calculate the total SSE by summing that column: =SUM(squared_residual_range)

  2. Coefficient of Determination (\(R^2\)):

    • Definition: The percentage of data variability explained by your model: \(R^2 = 1 - \frac{\text{SSE}}{\text{SST}}\)

    • Excel Syntax: Program this algebraic relationship directly using cell referencing to your calculated SSE and SST cells: =1 - (SSE_cell / SST_cell) OR using =RSQ(known_y_values,known_x_values)

    • Verification: Your calculated formula must yield the exact same \(R^2\) decimal value displayed on your scatter plot trendline to verify mathematical alignment.

Excel Trendlines#

Excel can also calculate the regression equation visually using a chart.

  1. Highlight your data columns and insert a 2D scatter chart.

  2. Right-click on a data point on the generated chart and select Add Trendline.

  3. Select the Linear option from the trendline menu.

  4. 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.

To calculate the correlation coefficient (\(R\)) directly from the chart, simply take the square root of the displayed \(R^2\) value using the formula =SQRT(chart_R_squared_value).

Data Visualization Standards#

When presenting regression models visually, engineers must follow standard formatting rules. Clear charts ensure the audience understands the physical context of the data and the modeling decisions made.

Follow these standards when creating your plots:

  • Chart Titles: Include a descriptive title explaining the specific relationship being modeled.

  • Axis Labels: Label both the x-axis and y-axis with the variable name and its corresponding units.

  • Raw Data: Represent actual collected data points using individual markers (a scatter plot). Do not connect raw data points with a line.

  • Regression Models: Represent the calculated best-fit model using a continuous line (a trendline) to clearly distinguish it from the measured data.

  • Legends: Every professional engineering chart presenting a regression analysis must include a clear, properly positioned legend—even if there is only one data series on the plot. This is essential to help the audience instantly distinguish between the discrete physical raw data points (represented as markers) and the continuous mathematical model (represented as a trendline).

Video Clarification

The provided Excel tutorial video demonstrates the step-by-step process for calculating linear regression. However, the video does not follow engineering best practices for data visualization. Use the video for help calculating regression values, but do not use it as a reference for how to format and present your plots.

Limitations, Interpolation, and Extrapolation#

Models are only valid within the range of the collected data. When using a calculated regression line to make predictions, engineers must understand the difference between two key methods:

  • Interpolation: The process of predicting or estimating a value that falls within the bounds of your existing, measured data.

  • Extrapolation: The process of predicting outcomes by using a regression relationship beyond the range of data used to create it.

Care must be taken when extrapolating a regression relationship beyond the measured values. For the pen spring, Hooke’s Law only applies within a specific linear range. If a spring is stretched beyond its physical limit until it deforms or fails, the relationship becomes non-linear. The calculated linear model cannot describe the behavior of a broken spring. For this reason, any use of extrapolation must always be accompanied by a clear engineering rationale.