Non-linear extrapolation graph

[cps.13]

Hi,

Please note: this is not a homework question! It is a real world problem I am trying to solve.

I have some values in mA and tonnes which I need to extrapolate but they are not linear. I know it the mA curve drops off the higher the tonne values go.

I have plotted the values in excel

mA tonne
7.63 42
10.83 84
13.86 126
15.17 147
unknown 210

I have got a logarithmic trend line which is -5.9517ln(x) - 14.903 but I am not 100% sure this is correct?

I then did =(-5.9517*LN(210))-14.903 to get my 210 value but this comes out with a high negative value!

I need to find my predicted mA value at 210tonne.

Can anyone help?

Thanks

 

[gleem]

Your trend line is not correct. It is an inherently negative function for all values of x>1.

 

[cps.13]

Your trend line is not correct. It is an inherently negative function for all values of x>1.

I thought it might be! Do you know how I calculate it correctly?

Thanks

 

[DEvens]

Why did you choose a log curve fit? A quadratic fits pretty well.

You say you plotted it in Excel. What would be so bad about adding a trend line and displaying the equation?

Edit: The log fit you have seems to be wrong by just the sign of the first term. And it's a crummy fit to the data.

 

[cps.13]

Why did you choose a log curve fit? A quadratic fits pretty well.

You say you plotted it in Excel. What would be so bad about adding a trend line and displaying the equation?

Edit: The log fit you have seems to be wrong by just the sign of the first term. And it's a crummy fit to the data.

I chose a log curve fit because I thought that was what you would use for non-linear data? perhaps I'm wrong there too.

I have added a trend line to display the equation, the equation in my OP was the equation generated by Excel.

I'm open to options other than log, but i want to keep it on excel as I have many sets of results to do this for.

thanks

This is why I chose log - I know my curve will drop off at the end and then level off

• Logarithmic trendlines: Where there is a sudden increase or decrease in the chart, which then continues on to become level.

EDIT: Sorry just notice that my equation is 5.9517ln(x) - 14.903 as per Excel. There is not a - in front of the 5.9517 like I put in my OP

 

[DEvens]

Yeah, try a polynomial fit to order 2. It looks a lot closer. It's just a couple lines down on the dialog when you add a trend line. Quadratic looks like a good fit.

Unless you have some good physical reason to suppose it is log, stay with the polynomial. And try no to go to too high order polynomial, again, unless you have some good reason to think the higher order terms are important.

So if you were doing something involving a sensor (as is suggested by your data being mA and tonne) then the manufacturer should give you some idea what the functional form is.

 

[cps.13]

Yeah, try a polynomial fit to order 2. It looks a lot closer. It's just a couple lines down on the dialog when you add a trend line. Quadratic looks like a good fit.

I have tried a polynomial line and know this gives an incorrect equation. The output at 20mA comes out higher than 20mA, I know for a fact it will be lower. The Log equation fits better but I think because of the lack of data (only 4 points) it does not generate a good line. Using the log equation to check known values it comes out wrong by ~0.50mA.

So if you were doing something involving a sensor (as is suggested by your data being mA and tonne) then the manufacturer should give you some idea what the functional form is.

The problem is, I am the manufacturer of the sensor! A bit of background, the product is for cable tension (wire strop), the problem is the customer has specified a wire which can only safely be pulled to 147tonne, but wants the equipment rated to 210tonne. So we cannot simulate anything above 147tonne. I know from previous experience that the output always drops off towards the top (so it will not be 20mA at 210tonne) which is fine as we can account for non-linearity in software, but, we need to know with reasonable accuracy what the mA figures would be!

Does anyone know a better way of doing it? I tried plotting the actual mA values vs the desired mA values and this gave me a better line but I'm open to suggestions!

Thanks,

 

[gleem]

The problem is, I am the manufacturer of the sensor! A bit of background, the product is for cable tension (wire strop), the problem is the customer has specified a wire which can only safely be pulled to 147tonne, but wants the equipment rated to 210tonne

So the safe working load is 147 tonnes which you can measure. But he wants a ultimate strength rating of 210 tonnes which you cannot measure. Is that correct?

 

[cps.13]

So the safe working load is 147 tonnes which you can measure. But he wants a ultimate strength rating of 210 tonnes which you cannot measure. Is that correct?

Nearly - the safe working load is 210tonne of the equipment. The particular wire strop we are using to calibrate has a SWL of 147t. So if we were to use a different wire strop we could apply 210tonne. But the wire strop with a 210t break load is incredibly expensive. The likelihood of it ever being used to 210t is remote, and all adequate documentation/warnings etc is being put in place to explain the calibration will be theoretical above 147t. But I still would like to get it as accurate as possible.

Thanks

 

[gleem]

Can you borrow or fabricate something that you can attach to your sensor other than an expensive strop? You didn't mention the accuracy of your current measurements . You quote to a hundredth of a mA. are they that accurate?

 

[cps.13]

Can you borrow or fabricate something that you can attach to your sensor other than an expensive strop? You didn't mention the accuracy of your current measurements . You quote to a hundredth of a mA. are they that accurate?

No we can't use anything other than the wire, other things don't replicate the pull accurately and the results/output changes. The accuracy generally is 2% FRO. The only option is extrapolation.

thanks

 

[gleem]

I looked at you data. I found the average slope in each interval. and then found the change in slope as the force was raised. It looks like the slope changes by a constant amount. I adjusted the current reading for 210 tonnes until the change in slope was consistent with the rest of the data. I got 18.2 ma.for 210 tonnes. Do it yourself to verify this.

 

[mfb]

A linear relation doesn't look so bad. A quadratic relation (load follows current squared) gives a better approximation. A polynomial of degree 3 fits exactly by construction.

Those three possible models lead to significantly different answers, ranging from 19.83 mA for the linear fit to 18.33 for the third order polynomial, with the quadratic at 19,08 in between. Based on those four data points alone with no other information, I wouldn't trust any estimate better than this.