Jul 06 2020 04:34 AM
When I have 2 sets of data in X and Y format, I often use Excel to draw smoothed curves between points, using "scatter charts with smooth lines or markers". I can easily do a linear interpolation between these points, but would like to find a way to interpolate between the points on the generated curve, as the linear interpolations create a stepped (non-smooth) appearance. There surely is an easy way, as the smoothed curves are so easily generated by the graphing function of Excel.
As an example, I can get some nice clean curves with x,y pairs such as 45,82; 40,84; 35,85; 28,85.5, but how can I find the y value coresponding to 37?
By the way I am using this in my hobby of aerodynamics, which requires some accuracy in the interpolation.
I hope someone can help.
Jul 06 2020 09:53 AM
@Johann664 I believe the default curve you are referring to is a spline fit that is not intended nor appropriate for a most data modelling (unless it is specifically a relationship that is based as smooth constrained points). Instead you should 'add trendline' choose the type of relationship the x and y data share (or you believe works best) and click 'Display Equation on chart'. Then you can use this actual equation to interpolate the value. That said: for the example in the sample set I chose a 3rd order polynomial to "match" the spline look and it found an equation of y= -0.0005x^2 + 0.0455x^2 - 1.3082x + 98.412. Substituting 37 into the equation results in: 86.9716 which is WAY off from what is actually displayed (i.e. approximately 84.7). In order to come up with a number that is close I changed the "a" term to be -0.000544 and got the expected 84.7 but that was a matter of a guessing game. So basically even with a 'known' equation based trend line, you are in trouble when the level of precision isn't properly shown (i.e. in this case Excel only displayed 1 significant digit on the highest order term).
I recommend going to an engineering software that is more designed for this type of work but doing some searches others have created interpolation for spline fits in excel:
https://newtonexcelbach.com/2009/07/02/cubic-splines/
https://www.dropbox.com/s/0rchmp8d1b1mrqa/cubic%20spline.xlsm?dl=0
But I caution a) I can't vouch for either of these and b) a spline fit although looks good, is often NOT the best real world solution