Plot Functions from a Few Data Points

[mike_302]

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?

 

[tusharm]

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]

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]

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.