Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

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


2 Quick Methods to Create Equation from Data Points in Excel

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.

Use Trendline Option to Create Equation from Data Points

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

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

Use Trendline Option to Create Equation from Data Points

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

Use Trendline Option to Create Equation from Data Points

  • 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.

Use Trendline Option to Create Equation from 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.

Use Trendline Option to Create Equation from Data Points

Read More: How to Graph Two Equations in Excel (with Easy Steps)


Similar Readings


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.

Utilize Solver Add-in to Create Equation from Data Points

  • 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”.

Utilize Solver Add-in to Create Equation from Data Points

  • 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.

Utilize Solver Add-in to Create Equation from Data Points

Step 2:

  • First, choose a cell (E5) and put the formula down-
=-0.0017*C5^2 + 5.7099*C5 - 3372.4

Utilize Solver Add-in to Create Equation from Data Points

  • 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 points value.

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

Utilize Solver Add-in to Create Equation from Data Points

  • 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.

Utilize Solver Add-in to Create Equation from Data Points

  • 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.

Utilize Solver Add-in to Create Equation from Data Points

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

Create Equation from Data Points in Excel

Read More: How to Find the Equation of a Line in Excel (With Quick Steps)


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.

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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

2 Comments
  1. 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.

    • 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

Leave a reply

ExcelDemy
Logo