How to Create Equation from Data Points in Excel

While working in Microsoft Excel Sometimes we might need to create equation from the data points plotted in a chart. Sometimes it might feel difficult. But from now on it won’t be a problem to make the equation from the data points. Today in this article, I am sharing with you how to create equation from data points in Excel.

How to Create Equation from Data Points in Excel: 2 Quick Methods

In the following, I have explained 2 simple and quick methods to create equations from data points in Excel.

Suppose we have a dataset of a manufacturing company’s Total Production and Target Production month-wise. Now we will make a chart and create equation from data points inside the chart.

1. Use Trendline Option to Create Equation from Data Points

One of the easiest ways to create equation from data points is to draw a trendline and then use it to form the equation. Follow the steps below-

Step 1:

First, select numeric data from the list and draw a “Scatter Chart” from the “Insert” option.

Secondly, you will get the scatter chart with data points inside the worksheet.

Hence, select any data point from the graph and right right-click the mouse button, and choose “Add Trendline” to draw the trendline.

Thereafter, choose “Linear” to draw a linear trendline and checkmark the “Display Equation on chart” to get the equation.

Therefore, you will get the equation just like the following screenshot inside the graph. Now we will use the equation to calculate the data points.

Step 2:

Starting with, select a cell (E5) and apply the following formula which we got from the chart-

=0.9026*C5 + 71.303

Gently, press Enter and drag the “fill handle” down to fill all the cells.
Finally, we have successfully created equation from data points in Excel.

2. Utilize Solver Add-in to Create Equation from Data Points

If you want you can also utilize the Solver Add-in to create equations from data points and minimize the gap. Follow the steps below-

Step 1:

Just like the previous method, draw a scatter chart with the numeric values from the list.

Here, this time we will create a “Polynomial Trendline” for a better understanding of the data. To do that, inside the chart click the right button and choose “Format trendline” from the options.

Then, select “Polynomial” and checkmark the “Display Equation on chart”.

Thus we will get the equation from the data points plotted in the chart. Now without wasting time we will apply the equation for the data points and minimize the difference with the “Solver” feature.

Step 2:

First, choose a cell (E5) and put the formula down-

=-0.0017*C5^2 + 5.7099*C5 - 3372.4

Simply, press Enter and drag down the “fill handle” to fill.
Thereafter we have the output in our hands. Now we will calculate the difference between the variable and data point value.

In the same fashion, choose a cell (F5) and apply the formula to determine the difference-

=D5-E5

Similarly, hit the Enter button and drag down the “fill handle” to fill with precious output.
From the result, we can see the difference is a bit much. To minimize it follow the instructions below-

Step 3:

Above all, we will calculate the total difference with the SUMSQ function. To do that select a cell (F14) and write the formula down-

=SUMSQ(F5:F12)

Where,

The SUMSQ function returns the sum of the squares value provided in the string.

Hit the Enter button and the result is in your hands.

In order to minimize the difference go to the “Solver” option from the “Data” option.

In the new window, select the total difference output cell in the “Set Objective” cell. Select the changing variables and remove the check mark from the “Make Unconstrained Variables Non-Negative” option.
Simply, press “Solve” to get the final output.

In conclusion, we have completely minimized the differences thus the chart has plotted a trendline over all the data points.

Things to Remember

If you are not getting the “Solver” option in your “Data” feature just follow the instructions to get it. Go to File > Options > Add-ins. Then checkmark the “Solver Add-ins” option and get it inside the “Data” ribbon.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Create Equation from Data Points.xlsx

Conclusion

In this article, I have tried to cover all the methods to create equation from data points in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. Stay tuned and keep learning.

<< Go Back To Trendline Equation Excel | Trendline in Excel | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Yogiraj Oct 29, 2022 at 11:59 AM

You just modified the original data set to match the trendline that excel has created in first place. What is the use of this?
It will be useful only when you are able to fine tune the equation excel gave to suit your data points and not change the data points to match the excel equation.
- Reply
  Wasim Akram Nov 1, 2022 at 11:12 AM
  
  Dear Yogiraj,
  
  Thank you for your comment.
  
  You are absolutely right about your opinion regarding its use.
  
  By creating equation from data points you can also see the difference between the plotted and actual data points.
  
  Thanks