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-
- 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 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.
- 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-
- 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.
- 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 points value.
- In the same fashion, choose a cell (F5) and apply the formula to determine the difference-
- 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-
- Above all, we will calculate the total difference with the SUMSQ function. To do that select a cell (F14) and write the formula down-
- 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.
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.
- How to Find Unknown Value on Excel Graph (4 Easy Ways)
- Display Equation of a Line in Excel Graph (2 Easy Ways)
- How to Determine Protein Concentration from Standard Curve in Excel
- Calculate Unknown Concentration from Standard Curve in Excel
- How to Graph an Equation in Excel Without Data (With Easy Steps)