In this tutorial, you will learn how to quickly find regression equations in Excel.
In statistics, a regression equation is used to determine whether or not there is a link between two sets of data. For instance, you might discover that a child grows roughly 3 inches a year if you measure their height each year. A regression equation can be used to model the three-inch growth tendency. In reality, most aspects of the real world—from gas prices to hurricanes—can be described using an equation of some form, which enables us to foretell the future.
Before moving to the tutorial, let’s familiarize ourselves with the Excel functions we will be using to quickly find regression equation using the Linest function.
Table of Contents
Anatomy of Linest Functions
LINEST(known_y’s, [known_x’s], [const], [stats])
The LINEST function determines the statistics for a line by determining the straight line that best fits your data using the “least squares” method, and it then returns an array that describes the line.
Once ready, we’ll get started by utilizing real-world examples to show you how to quickly find regression equations in Excel.
Find Regression Equation in Excel
Before we begin we will need a group of data to use ‘If Contrains’ formula in Excel.
First, you need to have a clean and tidy group of data to work with.
To calculate the simple linear regression equation for this dataset we can simply insert this formula =LINEST(A2:A11, B2:B11).
Once you are done, your Excel will look something like this.
Let’s interpret the returned values. The coefficient for the intercept is 0.9956 and the coefficient for the slope is 1.4434. We can create the equation for this straightforward regression model using these values with this formula y = 0.9956 + 1.4434(x).