This guide will explain how to perform cubic regression in Excel.
We use cubic regression to find an equation that best fits our data. We can then use this equation to predict future values.
Cubic regression is a process that generates a third-degree equation that fits a given set of data. Cubic regression is part of a family of regression techniques known as polynomial regression.
We often use the cubic equation when a given dataset follows a cubic curve.
If we want to find the cubic equation that fits our data, we can use the LINEST Excel function.
The LINEST function performs linear regression given a known set of x and y values. However, we can also use the function to calculate different types of regressions by specifying the exponents we need.
Since cubic regression returns a polynomial with a degree of three, we must find the coefficients for x, x², and x³.
Another alternative to finding the cubic regression of a dataset is to use the trendline tool. We’ll use a scatter plot to visualize the shape of our data and use the trendline feature to identify the equation that best fits our data.
Now that you know when to perform cubic regression in Excel, let’s learn how to use the LINEST function and trendline tool on an actual sample spreadsheet.
A Real Example of Performing Cubic Regression in Excel
The following section provides several examples of how to perform cubic regression in Excel. We will also explain the formulas and tools used in these examples.
First, let’s take a look at our sample data. We have a dataset of 12 values of x and y pairs. Our goal is to use cubic regression to find a polynomial that best fits our data.
We’ll use the LINEST function to return the coefficients of each term in our cubic polynomial.
To get these values, we just need to use a single formula:
=LINEST(B2:B13,A2:A13^{1,2,3})
Let’s take a look at how the LINEST function returns four different coefficients.
By default, LINEST follows the linear equation of y = mx + b. The function returns both the value of m and b given a range of x and y values. However, we can also use the LINEST function to return a polynomial equation by raising the power of the second argument.
We’ll use the curly brackets to indicate that we want the function to output a coefficient for each indicated power.
We can obtain a similar result by using the trendline feature on an Excel chart.
The scatter plot and trendline technique also enables you to see how well the cubic regression fits your dataset.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
Use our sample spreadsheet to see how the trendline changes if we modify the x and y values in our dataset.
If you’re ready to try performing cubic regression yourself, head over to the next section to read our step-by-step breakdown on how to do it!
How to Perform Cubic Regression in Excel
This section will guide you through each step needed to perform cubic regression in Excel. You’ll learn how we can use the LINEST function and trendline tool to find the cubic equation that fits our data closely.
Follow these steps to start performing cubic regression:
- First, create a table for each of the four coefficients we’ll need for the cubic polynomial.
- Next, we’ll use the
LINESTfunction to find an equation that fits our x and y values. We’ll add the range of y’s as our first argument.
- Add the range of known x values as the second argument of
LINEST.
- Next, type ‘^{1,2,3}’ after the cell range of known x values. This indicates that we want to find coefficients for x3 and x2 as well.
- Hit the Enter key to return the coefficients for each term in our cubic polynomial. In our example, we now know that the equation
y = 0.00157x³ + -0.22212x² + 9.89283x - 46.8973fits our dataset.
- Next, we’ll create a scatter plot and trendline to obtain the same result. We’ll first select both our x and y values.
- In the Insert tab, click on the first Scatter plot option.
- We’ll now add a polynomial trendline to our chart. Click on the ‘+’ button and check the Trendline option. By default, Excel will display a simple linear equation. Click on More Options to display the Format Trendline panel.
- In the Format Trendline panel, select the Polynomial option and set the Order to 3.
- Check the Display Equation on chart option.
- Your scatter plot should now have a cubic polynomial trendline. The equation displayed on the chart is equivalent to the coefficients returned by our LINEST function earlier.
These are all the steps you need to perform cubic regression in Excel.
This step-by-step guide should provide you with all the information you need to begin using the LINEST function to perform cubic regression.
You should now have a clear understanding of cubic regression and how you can visualize the cubic equation using a scatter plot graph in Excel.
Cubic regression is just one example of the many mathematical functions you can use in your spreadsheets. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.
With so many other Excel functions available, you can find one appropriate for your use case.
Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!
