Polynomial Regression Interpolation Curves

[Hawaii596]

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.

 

[IRstuff]

??? You have the equation, so you populate one column with the temperature values, and fill in adjacent column with the equation. What more do you need?

TTFN

FAQ731-376

 

[GregLocock]

I don't quite see what your problem is. Big hint, posting a sample worksheet will let some mighty, if lazy, brains loose on the subject.

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

http://eng-tips.com/market.cfm?

 

[corus]

TYou could just use the options under trendline and 'forecast' by how ever many you want.
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

 

[katmar]

Do not trust values extrapolated beyond the experimental data using polynomials.

Katmar Software
Engineering & Risk Analysis Software

http://katmarsoftware.com

 

[corus]

Better yet is to use confidence limits on your extrapolation. This is fairly easy to do with linear regression, but other functions I'm not sure about.

Tata

 

[Denial]

I'm not totally sure what it is you are trying to do. (GregLocock nailed me with his "lazy brain" comment.)

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.

 

[Hawaii596]

Thanks for the inputs. I ended up (for the moment - due to the deadline) finding and using CurveExpert.exe V1.4. I still have a fairly complex workbook, as I have to generate tables. It is an industry practice in such cases to make three measurements (or so), as this is the extent of valid measurement points for my needed accuracy, then extrapolating. In my case, I am able to measure at the freeze points of Zn, Al, and Cu (419.527, 660.323, and 1804.64 Deg C respectively). I measure the millivolts output from the type R thermocouple, and develop a mV to Deg C table over the range of the thermocouple (in this case, I develop a worksheet from 100 to 1200 Deg C in 5 degree increments.

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.

 

[katmar]

Seeing that you have been open about your lack of formal math training let me give you a few tips. But if this is homework please be aware that you are not allowed to post homework problems here and it will likely be deleted shortly.

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

 

[Hawaii596]

I'm 53 years old, so it is definitely not homework. I am the metrology engineer at my lab, based on my military background and a lot of metrology knowledge. But unfortunately, not the engineering degree.

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.

 

[IRstuff]

Something doesnt' make sense. Thermocouples and RTDs are supposed to very well behaved, so arbitrary polynomial fits seem at odds with that notion. I could see tweaking the standard values a bit:

http://srdata.nist.gov/its90/type_r/rcoefficients.html,

but note that NIST apparently does not support the notion of covering the entire span that you've specified with a single equation.

TTFN

FAQ731-376

 

[siretb]

I think that Katmar and Irstuff are quite right.
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