Forum Discussion
Interpolation of points on a smoothed Excel chart curve
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