Plot Functions from a Few Data Points

mike_302

New Member
Joined
Oct 6, 2017
Messages
11
I am working on a tidy little workbook to help me and other users to (1) generate approximately-accurate polynomial curves for a few data points we know, for 2 curves, then (2) to add those 2 curves together.

The first part is causing a struggle. I have 2 columns and another 2 columns (for the two sets of data points). A user will know the two end points of the curve, so they'd list those two data points. They might also know the middle point, so they'd add that to the data sets. Now you have 2 datasets, each with 3 points, and these can be plotted on Scatter X,Y pllots. The user would also know that they want a linear curve, quadratic, cubic, or quartic curve to be fit to those points. Trouble starts here: We actually want to visualize this curve as a function of 'y' data points, i.e. x is a function of y. Is this possible, to begin with?

Next after excel generates a regression formula that the user is happy with, to move to action (2), I think I need to populate a list of common data points -- for more complex functions, or where one data set doesn't quite align with the second data set, you can't simply add these together... The list of common data points would be much more populated than the initially user-input data points. A user could, of course, manually do this, by taking the regression function from the graphs and generating the longer list of data points manually. Then they simply add the X-values at the common Y-points, and everything is complete... But it's the intermediate step of taking the regression function and populating that list of regression points to a common set of Y-values. Any thoughts on this?

I also feel like this is a common problem that engineers face, so I wonder if there's a guide or a trick to doing this. i.e. Engineers often know a few characteristic points for a curve, an they know the theoretical curve is defined by a certain function (second, third, or fourth order). They want to input those points, get the curve drawn (brings us to the end of my step (1)), then maybe they want to superimpose two curves on each other (my step (2)).

Thoughts?
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
1. If you know what kind of function you want to fit to the data, you can get Excel to give you the coefficients that "best fit" the data. See Trendline coefficients, particularly the section on Regression with polynomials

The big caveat...

With 2 data points, one can fit a straight line through it. With 3 data points, that becomes a polynomial of order 2, i.e., a quadratic. And, after that, you can fit an infinite number of higher order polynomials to the data set.

If that isn't clear, start with 1 data point. You can fit an infinite number of straight lines through it.

Next, consider 2 data points. You can fit only 1 straight line (linear equation y=mx+c) through them. But, you can fit an infinite number of quadratics (polynomial order 2) through the 2 points.

Keep on extending that to additional data points and polynomial orders.

2. Excel doesn't care (or even understand) which is the X and which is the Y series. You can specify any range (column, for example) as the X and any range as the Y.

3. I don't understand what you want to do in what you call action (2). Maybe, you are trying to estimate how usefulness of the generated regression function. If so, you should look at how to use 'residuals.'
 

mike_302

New Member
Joined
Oct 6, 2017
Messages
11
1. If you know what kind of function you want to fit to the data, you can get Excel to give you the coefficients that "best fit" the data. See Trendline coefficients, particularly the section on Regression with polynomials

The big caveat...

With 2 data points, one can fit a straight line through it. With 3 data points, that becomes a polynomial of order 2, i.e., a quadratic. And, after that, you can fit an infinite number of higher order polynomials to the data set.

If that isn't clear, start with 1 data point. You can fit an infinite number of straight lines through it.

Next, consider 2 data points. You can fit only 1 straight line (linear equation y=mx+c) through them. But, you can fit an infinite number of quadratics (polynomial order 2) through the 2 points.

Keep on extending that to additional data points and polynomial orders.

2. Excel doesn't care (or even understand) which is the X and which is the Y series. You can specify any range (column, for example) as the X and any range as the Y.

3. I don't understand what you want to do in what you call action (2). Maybe, you are trying to estimate how usefulness of the generated regression function. If so, you should look at how to use 'residuals.'
I gather your solution is essentially to do Action (1) solely with functions, and not so much plotting and regressing with the graph functions. That's fair enough. I'll give that a try. It might take me a bit of extra thinking to enact something that also gives the user immediate feedback on what their curve looks like.

Action (2) is simply to add the two curves together. But addition of two curves means you need common points. Hence, I was trying to regress a curve, because then I can use the regression formula to define a common set of points (e.g. define 20 common points along the relevant range of the curves). With the common set of points on both curves, I can add the curve values at those points.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
I gather your solution is essentially to do Action (1) solely with functions, and not so much plotting and regressing with the graph functions. That's fair enough. I'll give that a try. It might take me a bit of extra thinking to enact something that also gives the user immediate feedback on what their curve looks like.

Action (2) is simply to add the two curves together. But addition of two curves means you need common points. Hence, I was trying to regress a curve, because then I can use the regression formula to define a common set of points (e.g. define 20 common points along the relevant range of the curves). With the common set of points on both curves, I can add the curve values at those points.
Plotting something graphically is always useful. So, go ahead and do that. However, the regression function computed and shown in the chart is not easy or convenient to use for analysis. That's where the LINEST function comes in.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,246
Messages
5,595,044
Members
413,963
Latest member
teggl97

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top