Sometimes linear regression doesn’t quite cut it – particularly when we believe that our observed relationships are non-linear.  For this reason, we should turn to other types of regression.  This page is a brief lesson on how to calculate a quadratic regression in Excel.  As always, if you have any questions, please email me at MHoward@SouthAlabama.edu!

---

The typical type of regression is a linear regression, which identifies a linear relationship between predictor(s) and an outcome.  Sometimes our effects are non-linear, however.  In these cases, we need to apply different types of regression.

A common non-linear relationship is the quadratic relationship, which is a relationship that is described by a single curve.  In these instances, the relationship between two variables may look like a U or an upside-down U.  Often, we call the latter of these relationships (the upside down U) a “too much of a good thing” effect.  That is, when one variable goes up, then the other goes up too; however, once you get to a certain point, the relationship goes back down.  For example, conscientiousness may relate to life satisfaction.  If you are hard working, then you are generally happier with your life.  However, once you get to a certain level of conscientiousness, your life satisfaction might go back down.  If you are too hard working, then you may be stressed and less happy with your life.

There is more that could be stated about quadratic regression, but we’ll keep it simple.  To calculate a quadratic regression, we can use Excel.  If you don’t have a dataset, you can download the example dataset here. In the dataset, we are investigating the relationship of conscientiousness and life satisfaction.

The data should look something like this:

If your dataset looks differently, you should try to reformat it to resemble the picture above. The instructions below may be a little confusing if your data looks a little different.

First, we could create a scatter plot of the relationship between conscientiousness and life satisfaction.  As you can see, there is a clear U-shape to the data, which indicates that quadratic regression should be applied.

To perform a quadratic regression, we first need to create a new variable.  To do so in Excel, we should first right-click on our outcome column, and then click on Insert.

This creates a new column.  In this new column, we want each cell to be the square of our respective predictor observation.  To do so, first add a label to the first cell in the column, such as ConSQ.  Then, type “=A2^2” into the second cell of the column (without quotations).  This will automatically calculate the square of whatever is in the second cell of the first column.

To do this for the rest of the cells, you can double-click the bottom right of the cell with the formula in it.  If you do it correctly, it should automatically copy your formula into each of the following cells, and your Excel spread sheet should look like the following:

Once you have the squared values, we are going to perform a regression as usual.  Click on Data Analysis.

Then Regression and OK.

Then, click on the button below to identify your outcome data (your Y-Range).

Highlight your outcome data, including the label.  Then click the button shown below.

Now, click the button below to identify your predictor data (your X-Range).

Now, highlight BOTH your predictor variable and its squared values, including their labels.  Then click the button shown below.

Lastly, click on the box for Labels and press OK.

We should get results!  Yay!

If you need help reading this table, take a look at my Regression in Excel guide.  Otherwise, we can clearly see that the unstandardized beta for conscientiousness is -23.864, and the unstandardized beta for its squared values is 3.106.  Both of these are statistically significant (p < .001).  When interpreting quadratic effects, however, we only interpret the significance of the highest-order effect – in this case, the squared predictor.  So, we would say that a significant quadratic effect was seen between conscientiousness and life satisfaction, and the relationship could be described by a single curve.  We would then look at the scatter plot between the two to identify the shape of the curve, which resembled a U.  Lastly, we could identify that the overall R-Square of the model was .78, which is very high for the social sciences!

Of course, the results provide other information, which may be useful for your certain purposes, but the current guide just covers the basics.

Now you should be able to perform a quadratic regression in Excel.  As always, if you have any questions or comments, please email me a MHoward@SouthAlabama.edu!