One way to create an equation to summarize a set of data points in Excel.
The following step-by-step example shows how to do so.
Step 1: Enter the Data Points
First, let’s enter the following dataset into Excel:
Our end goal will be to find an equation that summarizes the relationship between the x-values and the y-values in this dataset.
Step 2: Insert Scatter Plot to Visualize Data Points
Next, highlight the cell range A2:B14, then click the Insert tab along the top ribbon, then click the Scatter icon within the Charts group.
The following scatter plot will be created:
Step 3: Create Equation from Data Points
Next, click anywhere on the chart. Then click the tiny green plus sign that appears in the top right corner, then click the dropdown arrow next to Trendline, then click More Options:
In the Format Trendline panel that appears on the right side of the screen, check the box next to Display Equation on chart:
The trendline equation that describes the relationship between the x-values and the y-values will automatically be displayed on the chart:
Step 4: Interpret Equation
In the plot we can see the following trendline equation:
y = 0.55232x + 2.40112
Here is how to interpret this equation:
- A one-unit increase in x is associated with an average increase of 0.55232 in y.
- When x is equal to zero, the average value of y is 2.401163.
We can use this equation to predict the y-values in the dataset based on the x-values.
For example, if the x-value is equal to 10 then we would predict that the y-value would be equal to 7.92:
- y = 0.55232x + 2.40112
- y = 0.55232*10 + 2.40112
- y = 5.5232 + 2.40112
- y = 7.92
We can use this same equation to predict the value of any y-value based on x-values.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Create a Line of Best Fit in Excel
How to Find Regression Equation in Excel
How to Plot an Equation in Excel
