Polynomial Regression Interpolation Curves
Polynomial Regression Interpolation Curves
(OP)
I'm working on emf output of thermocouple calculations, and need to develop a 3rd order polynomial regression curve. For example, I make measurements at 419.527, 660.323 and 1084.62 Degrees Celsius and measure the mV output of the type R thermocouple. The nominal values are 3.61123, 6.276875, and 11.640679 mV DC respectively. I am able to create a linear regression curve using the X-Y Scatter graph, setting the polynomial as type, and order to 5 (for my needs). I also have the graph show the polynomial formula. And I produce a nice graph.
Where I am stuck, is on how to extract a data set. What I need to do, as I am actually measuring at three points, is to extrapolate an interpolation chart with (for example) extrapolated values in 5 Degree Celsius increments starting at 100 Deg C, through 1200 Deg C. I need to plot the data for the predicted mV output of the thermocouple probe at the full set of data points above.
I've been able to create the chart, but can't yet figure out how to create the data set. I've thought about typing in the full set of nominal data points (i.e.: 100, 105, 110, ..... 1195, 1200 Deg C). Then entering a formula corresponding to the Y=ax2 +bx +c derived as in the chart above. But unfortunately, as a non-degreed type, this is pretty intense.
Any of you Excel experts able to give me some hints on this. I have an urgent requirement to develop this in the next day or so.
Where I am stuck, is on how to extract a data set. What I need to do, as I am actually measuring at three points, is to extrapolate an interpolation chart with (for example) extrapolated values in 5 Degree Celsius increments starting at 100 Deg C, through 1200 Deg C. I need to plot the data for the predicted mV output of the thermocouple probe at the full set of data points above.
I've been able to create the chart, but can't yet figure out how to create the data set. I've thought about typing in the full set of nominal data points (i.e.: 100, 105, 110, ..... 1195, 1200 Deg C). Then entering a formula corresponding to the Y=ax2 +bx +c derived as in the chart above. But unfortunately, as a non-degreed type, this is pretty intense.
Any of you Excel experts able to give me some hints on this. I have an urgent requirement to develop this in the next day or so.





RE: Polynomial Regression Interpolation Curves
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Polynomial Regression Interpolation Curves
Having said that I'd a thunk google would find someone who has done this before.
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Polynomial Regression Interpolation Curves
If you're wanting to use the trendline curve in cells then I copy and paste the formula into a cell and then edit it to replace the x2 etc. in the formula to A1^2 etc. Then copy an dpaste that expression into however many cells I need.
Tata
RE: Polynomial Regression Interpolation Curves
Katmar Software
Engineering & Risk Analysis Software
http://katmarsoftware.com
RE: Polynomial Regression Interpolation Curves
Tata
RE: Polynomial Regression Interpolation Curves
However I suspect that you need to use the LINEST function rather than rely on the trendline polynomial equation presented in the X-Y graph.
RE: Polynomial Regression Interpolation Curves
I'm working feverishly on finishing the worksheet, using (for lack of time) tabular data based on the CurveExpert output with LaGrangian polynomial plot.
I'm able to do it that way. But as I don't have quite the extensive math background (non-degreed), although I see the formula, and know how to make the X-Y Scatter generate it based on three temps (above) and three associated mV values, I'm struggling with how to convert the formula into a 5 Degree incremented table as described above.
My first priority this morning is getting the chart out. Once I've done that, I'll make a version removing any proprietary information and post it so I can get some inputs.
Thanks everyone for the numerous responses. Hopefully by late today or tomorrow I'll have time to post a version of the spreadsheet to help "extrapolate" the formulae needed.
RE: Polynomial Regression Interpolation Curves
First off, you cannot generate a unique 3rd order polynomial through 3 points. There are an infinte number of 3rd order polynomials that will fit the 3 points exactly.
Are you expecting a straight line? It is a long time since I did anything like this so I do not remember the expected relationship between temperature and voltage. I plugged the numbers into Excel and it looks pretty much like a straight line. Excel gave the equation of the straight line as temperature = 82.294*volts + 130.93 It would be very easy to generate a calibration curve from this.
You can fit a unique 2nd order (quadratic) equation through 3 points, but doing that says that you have no experimental error and I would not trust a curve generated that way.
Katmar Software
Engineering & Risk Analysis Software
http://katmarsoftware.com
RE: Polynomial Regression Interpolation Curves
No, I don't expect a straight line. Matter of fact, it is a slightly irregular upward curved plot. In the actual application, due to real-world limitations, I am only able to make a few measurements. The limits are the available "Fixed Points" (that is, ultra-pure metal ingots which are melted and allowed to "freeze" (re-solidify).
The so-called "freezing point" of the metals we have (Zn, Al, Cu) are the three points I have available. Using CurveExpert, I have gotten a pretty nice plot. What I did to model it is I have a copy of the ideal mV to Degrees C in 1 degree increments across the entire range of the Type R thermocouple (from about -50 to 1760 Deg C. My area of interest is only from about +100 to +1300 C. So I calculated ideal numbers for the three fixed points (at 419.527, 660.323 and 1084.62 C, and their corresponding ideal mV outputs. Using Curve Expert, and a Lagrangian Polynomial Interpolation, I came up with a pretty close approximation. It was within a few hundredths of a degree C some of the time. However, in the intervening areas (halfway between each of the three points, halfway between the lowest point and zero, and up at 1300 Deg C, I also had up to a 1 degree C error in the curve (because it is not precisely fitted to a standard mathematical model). So I applied interpolated correction factor to bend the curve at 100, 540, 872.5 and 1300, and included them in the data I ran through Curve Expert. After doing that, I got very good results. And because this will be used to test thermocouples that are not ideal, I realted my correction factors as a proportionate correction based on readings on the actual thermocouples at each of the fixed points.
I get really nice numbers, that match well with known parameters.
My remaining problem is, however, that I have to use Curve Expert to do the curve for me. So our technicians run the measurements, I take their measurements and input them into Curve Expert, have Curve Expert generate a table, then I copy and paste the table into Excel and generate my formal report from that.
What I WANT to do is have my Excel so that the Technicians can just input their readings into a locked down workbook and automatically generate the reports. But first, I have to master how to manipulate this formula (which is the one little detail I'm struggling with.
More later once I've finished getting this report out today.
Thanks again, all.
RE: Polynomial Regression Interpolation Curves
http
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Polynomial Regression Interpolation Curves
Something is wrong somewhere.
You speak of third degree polynomials with 3 data points. Thermocouples and RTD behave smoothly.
At any rate I would NOT use an interpolating polynomial oh a high degree, forcing a match at each measured point.
Either you have 3 measurements,3 data points, and quadratic will do.
The formula for getting the a, b and c coefficients (t=a (mV)^2 + b mV + C is straightforward. Do you need it?
Or you have more data //or additional unstated constraints// and then I would note fit a polynomial of high degree.
Think about splines.
Bernard