×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Polynomial Regression Interpolation Curves

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.   

RE: Polynomial Regression Interpolation Curves

??? 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: Eng-Tips.com Forum Policies

RE: Polynomial Regression Interpolation Curves

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: Eng-Tips.com Forum Policies  http://eng-tips.com/market.cfm?

RE: Polynomial Regression Interpolation Curves

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  

RE: Polynomial Regression Interpolation Curves

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

Katmar Software
Engineering & Risk Analysis Software
http://katmarsoftware.com

RE: Polynomial Regression Interpolation Curves

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  

RE: Polynomial Regression Interpolation Curves

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.
 

RE: Polynomial Regression Interpolation Curves

(OP)
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.

RE: Polynomial Regression Interpolation Curves

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

RE: Polynomial Regression Interpolation Curves

(OP)
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.

RE: Polynomial Regression Interpolation Curves

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: Eng-Tips.com Forum Policies

RE: Polynomial Regression Interpolation Curves

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
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources