How do you extrapolate data in Excel

Extrapolate values

The short way to the goal: job programming

Subject: extrapolating values by: jope
Written on: 06/07/2020 15:15:30

Hello, I have the following concern:

I have a lot of values ​​for a strain curve. The values ​​show the elongation behavior of a plastic at a given force over 5 minutes. Now I should extrapolate the value at 50 minutes and display it in a double logarithmic diagram. I have now done that with the trend function, included all x & y values ​​and set the new x value to 50. Now I come to a value of 13.979975%.

Is the approach correct?

Enclosed again the file to understand ...

Would be very grateful if someone could confirm that or have a tip.


Applies to: AW: extrapolate values
by: Sulprobil
Written on: 6/7/2020 4:34:21 PM

Hello Jope,

of course, if you use TREND, this function comes back with the (insane) value 13.9799975.
A simple linear extrapolation would return you 2,965. Not stunning either.
However, if you take a sympathetic look at your diagram, you might get the idea that the stretch is like a somewhat stretched and shifted logarithmic curve.
Now you do the approach y = a * LN (b * x) + c and let the Excel Solver determine the parameters a, b, and c for you, which minimize the square root of the sum of the difference squares to your elongation values.
The result is: a = 0.417606498648659; b = 6.9212577336493; and c = 1.49096365334816
Then you insert your target value x = 50 and get 3.93255037427525.
Here is my quickly assembled file (without any guarantee, but I use an up-to-date virus scanner):

This assignment looks like a teacher’s little trap. Maybe I trudged into it :-)

Have fun,
Bernd P

Subject: AW: plastics "creep"
by: Sulprobil
Written on: 6/7/2020 4:40:49 PM

See for example: Page / vsc / de / ch / 9 / mac / physik / deform_festk / anelast / anelast.vscml.html

Already tapped into it :-)

Subject: AW: plastics "creep"
by: jope
Written on: 6/7/2020 5:52:56 PM

Hello Bernd, first of all thanks to you.

Trap, does it mean that it doesn't stretch for 50 minutes, but rather stretches up to the first local maximum of the stress-strain diagram? Or do you mean something else?

In this example and the double log representation, however, 3.9 would then be correct.

Here is a contribution written by fcs. There you can see it graphically.

Applies to: AW: extrapolate values
by: fcs
Written on: 6/7/2020 4:59:14 PM

Hello Jope,

here you cannot work with a linear extrapolation.
A logarithmic extrapolation provides a much better adaptation to the existing measurement data.

However, it is a bit daring to extrapolate measured values ​​from 5 minutes by a factor of 10 to 50 minutes. Unless there are empirical values ​​for this type of material (here plastic HD-PE) that allow such an extrapolation.
Because materials can behave very differently when they are exposed to stress.

When using a logarithmic time axis, the trend line becomes a straight line. Here you can see very nicely how the trend line approaches the measured values ​​a few seconds after the start of the test.

And the difference in the result is serious:
with linear extrapolation: 13.9%
with logarithmic extrapolation approx. 3.9%

Here the 2 variants of the extrapolation graphically in the Excel diagram with the associated function.

Logarithmic with logarithmic time axis


Another tip, you should specify the extrapolated value of the elongation with a maximum of one decimal place.
The accuracy is no more, especially if you include the start-up area of ​​the test in the trend line.

Applies to: AW: extrapolate values
by: jope
Written on: 6/7/2020 5:57:29 PM

Hey fcs, great. That looks very promising.

Thanks for the graphic provision!

I think that the daring factor 10 is acceptable here, since the upper yield point (yield stress) is the first local maximum in the stress-strain diagram.

Excel Examples on "Counting Colors"