Data Regression with Excel

Excel Data Regression

A frequent activity for scientists and engineers is to develop correlations from data. By importing the data into Excel, data analysis such as statistics, trending, or calculations can be made to synthesize the information into relevant and actionable information. This tutorial demonstrates how to create a linear or polynomial functions that best approximate the data trend, plot the results, and perform a basic statistical analysis.

Linear and Polynomial Regression

Nonlinear Regression

Excel Nonlinear Regression Example Spreadsheet

Both regression tutorials can also be completed with Python and Matlab. Click on the appropriate link for additional information.

There is additional information on regression in the Data Science online course.

Generative AI Learning

Use these prompts to test your understanding after completing the tutorial. Direct the AI - do the fits yourself; use it to challenge your model choices.

"Quiz me with 5 questions, one at a time, on regression in a spreadsheet: what least squares actually minimizes, the three ways to fit a line (chart trendline, LINEST, and Solver minimizing a sum-of-squares cell) and when each is appropriate, what R-squared measures and one way a high R-squared can still mean a bad model, why a 6th-order polynomial through 7 points is a fit but not a model, and why nonlinear regression with Solver needs sensible starting values. Grade my answers and list my misconceptions."

"I fit a polynomial of order {n} to {m} data points and got R-squared = {value}. Cross-examine me: ask what the fitted curve does between and beyond my data points, whether the coefficients have physical meaning, and what I would predict at an input 20% beyond my data range. Then explain when a lower-order fit with worse R-squared is the better engineering choice."

Modern data: Battery test benches produce exactly this problem: discharge capacity measured over hundreds of charge cycles. Fitting capacity vs cycle number (linear early, often nonlinear later) and extrapolating to the 80%-capacity retirement point is a regression-plus-judgment exercise - the fit is easy, defending the extrapolation is engineering.

Discussion Questions

Two models fit the same battery-fade data: a straight line with R-squared 0.97 and a 5th-order polynomial with R-squared 0.995. Which do you trust to predict 200 cycles ahead, and why?
The chart trendline and LINEST give the same coefficients; Solver gives slightly different ones. What would make Solver's answer differ, and how do you check which is right?
R-squared compares the model to a horizontal line at the mean. Describe a data set where that comparison flatters a useless model.