Want to Use Excel for 1st, 2nd, 3rd Order Regression

QI Macros can help!

Run 1st-2nd-3rd Order Regression using QI Macros:

  1. Select your data.
  2. Click on QI Macros menu > Box, Dot Scatter & Scatter.
  3. QI Macros will draw a Scatter Plot for you.
  4. Adjust the Trendline using Polynomial instead of linear.

Use QI Macros Scatter Plot As a Starting Point

A QI Macros user recently called with what looked like a homework assignment. Normally, we don't help students with their homework, but I took a look anyway.

He wanted to know if QI Macros could do 2nd and 3rd order regression on his data.

Since no one has asked me this question in almost 20 years of doing business, I had to Google the answer. The answer is simple.

You can do it in native Excel.

data

Here Is How to Calculate Second and Third Order Regression

1. Draw a scatter plot of the data. QI Macros scatter plot will automatically give you the first order linear equation. This gives us the first order answers: 2.39 and 2.86:

1st Order Scatter Plot

As you can see, the trend line isn't a bad fit (R² = 0.9786), but we can investigate further.

2. Next, right click on the trend line and select Polynomial which gives us the second order answers (-0.22, 3.92, 0.82):

2nd-order-scatter-plot

This trend line is a better fit (R2=0.9961).

3. Next, change the Polynomial order to 3 and you get the third order answers (-0.066, 0.476, 1.82, 2.48):

3rd-order-scatter-plot

This trend line is a slightly better fit: (R2=0.9989).

Pretty simple, isn't it?

Here's my point: 

There's a lot of power hidden in Excel and I keep discovering more every day. Think outside the spreadsheet. What else can you do with Excel?

Stop Struggling with Scatter Plots for Regression!
Start creating your Scatter Plots for Regression in just minutes.

Download a free 30-day trial. Get Scatter Plots for Regression now!

Other Charts Included in QI Macros for Excel