Interpolation of points on a smoothed Excel chart curve

Copper Contributor

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.

1 Reply

@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