Excel interpolation by second degree polynomial
Solved
Heinoss
-
Zbob -
Zbob -
Hello,
I am currently working on adapting an Excel program from my company that allows for interpolating corrected values from a measurement device based on the calibration certificate data, to other types of devices.
To do this, the workbook models the values using a third-degree polynomial whose coefficients are determined by the least squares method (according to the person who created the original workbook).
The problem is that certain devices are calibrated on only three values, and using a third-degree polynomial is no longer possible. I therefore need to revert to a second-degree polynomial.
The issue is that the formulas for calculating the coefficients of the polynomial are quite complex, and I am unable to adapt them.
I would therefore like to know if Excel has a function that allows for directly providing these coefficients.
Thank you!
Configuration: Windows XP / Internet Explorer 8.0
I am currently working on adapting an Excel program from my company that allows for interpolating corrected values from a measurement device based on the calibration certificate data, to other types of devices.
To do this, the workbook models the values using a third-degree polynomial whose coefficients are determined by the least squares method (according to the person who created the original workbook).
The problem is that certain devices are calibrated on only three values, and using a third-degree polynomial is no longer possible. I therefore need to revert to a second-degree polynomial.
The issue is that the formulas for calculating the coefficients of the polynomial are quite complex, and I am unable to adapt them.
I would therefore like to know if Excel has a function that allows for directly providing these coefficients.
Thank you!
Configuration: Windows XP / Internet Explorer 8.0
7 answers
Hello,
If I understand correctly, you want to fit your points to a second-degree polynomial and derive the equation of the curve?
It's possible with Excel; first, you select the points you want to plot (a scatter plot is a good choice!), then once plotted, with the chart title, axis names, etc., you right-click on the curve and choose "Add Trendline" or something like that, and there you have different types of fittings, including second-degree curves.
Then in the options, you hide the box: "Display Equation on Chart" for the curve.
You will thus obtain your coefficients.
Catch you later!
--
The sacred formula of positivism: love as principle, order as foundation, and progress as goal. (Auguste Comte)
If I understand correctly, you want to fit your points to a second-degree polynomial and derive the equation of the curve?
It's possible with Excel; first, you select the points you want to plot (a scatter plot is a good choice!), then once plotted, with the chart title, axis names, etc., you right-click on the curve and choose "Add Trendline" or something like that, and there you have different types of fittings, including second-degree curves.
Then in the options, you hide the box: "Display Equation on Chart" for the curve.
You will thus obtain your coefficients.
Catch you later!
--
The sacred formula of positivism: love as principle, order as foundation, and progress as goal. (Auguste Comte)
As soon as you change your data, everything gets updated ...
--
Retirement is good! Especially in the Caribbean ... :-)
☻ Raymond ♂
--
Retirement is good! Especially in the Caribbean ... :-)
☻ Raymond ♂
Hi Green Day, and thanks for your reply.
I had indeed thought of a solution like this, but the problem is that the entire process needs to be automated.
Basically, the operator just has to enter the calibration values and let it run, and I'm afraid that even if we manage to automate this, it might be quite cumbersome to execute.
I had indeed thought of a solution like this, but the problem is that the entire process needs to be automated.
Basically, the operator just has to enter the calibration values and let it run, and I'm afraid that even if we manage to automate this, it might be quite cumbersome to execute.
hello
Through three points with different x-coordinates, and with at least two different y-coordinates, there is a unique parabola
with the equation y = ax² + bx + c
if these conditions are met, it is not an interpolation problem
with a relatively simple matrix calculation, one can determine a, b, c without going through a trend line see
https://www.cjoint.com/?0FcjoakxIdl
otherwise, it is indeed an interpolation problem
best wishes
Through three points with different x-coordinates, and with at least two different y-coordinates, there is a unique parabola
with the equation y = ax² + bx + c
if these conditions are met, it is not an interpolation problem
with a relatively simple matrix calculation, one can determine a, b, c without going through a trend line see
https://www.cjoint.com/?0FcjoakxIdl
otherwise, it is indeed an interpolation problem
best wishes
Hello to both of you,
And I apologize for taking so long to respond (long weekend, you know)
I mentioned interpolation because the program needs to calculate the corrected values that correspond to all the read values that can be obtained from the device, based on the coefficients found.
But otherwise, the problem is indeed the one described by ccm81.
The only issue is that I am required to keep the same "form" of calculations as the program I'm working with, and my predecessor did not use a matrix system.
So I find myself with a third-degree polynomial with coefficient calculation formulas like:
a =(COVARIANCE(E45:E51;C45:C51)-COVARIANCE(B45:B51;C45:C51)*COVARIANCE(B45:B51;E45:E51)/VAR(B45:B51;B52)-E6*(VAR(E45:E51;E52)-COVARIANCE(B45:B51;E45:E51)*COVARIANCE(B45:B51;E45:E51)/VAR(B45:B51;B52)))/(COVARIANCE(E45:E51;I45:I51)-COVARIANCE(B45:B51;I45:I51)*COVARIANCE(B45:B51;E45:E51)/VAR(B45:B51;B52))
so it's quite difficult to decipher as it stands, even if when I look at the ranges of data used, I somewhat recognize the calculation formula in the case of a linear regression: a = COV(XY)/V(X) b = E(Y) - aE(X)
Still, it remains a good obscure mush in my eyes, and I haven't found any method online for calculating results like this for degrees higher than 1.
And I apologize for taking so long to respond (long weekend, you know)
I mentioned interpolation because the program needs to calculate the corrected values that correspond to all the read values that can be obtained from the device, based on the coefficients found.
But otherwise, the problem is indeed the one described by ccm81.
The only issue is that I am required to keep the same "form" of calculations as the program I'm working with, and my predecessor did not use a matrix system.
So I find myself with a third-degree polynomial with coefficient calculation formulas like:
a =(COVARIANCE(E45:E51;C45:C51)-COVARIANCE(B45:B51;C45:C51)*COVARIANCE(B45:B51;E45:E51)/VAR(B45:B51;B52)-E6*(VAR(E45:E51;E52)-COVARIANCE(B45:B51;E45:E51)*COVARIANCE(B45:B51;E45:E51)/VAR(B45:B51;B52)))/(COVARIANCE(E45:E51;I45:I51)-COVARIANCE(B45:B51;I45:I51)*COVARIANCE(B45:B51;E45:E51)/VAR(B45:B51;B52))
so it's quite difficult to decipher as it stands, even if when I look at the ranges of data used, I somewhat recognize the calculation formula in the case of a linear regression: a = COV(XY)/V(X) b = E(Y) - aE(X)
Still, it remains a good obscure mush in my eyes, and I haven't found any method online for calculating results like this for degrees higher than 1.
may God guide you on His path