How to draw a line parallel to the linear portion of the curve in excel?

Question

I have stress strain data from tensile test. I have drawn a stress vs strain graph in excel.I need to find the yield point.For that, I need to draw a line parallel to the linear(straight) portion of the curve with 0.2% offset in x axis and see where it intersects with the original curve.

So, I tried to keep only the linear portion data and drew a trendline, which gives me straight line equation y=mx. Now, if I want 0.2% offset, equation of line is y=mx +c. I have the equation, how to draw this line in excel with the equation? And how to get the intersection point? Is my approach right? Please help.

Answer 1

A Stress-strain curve with data courtesy of YouTube:

It can be observed that this is linear (elastic deformation) for about the first nine data points. Hide the other data points and add a liner trend line to the chart, with Display Equation on chart. This shows y=4232.x + 0.701.

In say N1 =4232*D2+.701 and copied down to N10 provides the data for an additional data series. Copy N2:N10, select the chart, Paste and format the added series to see the match:

For a 0.2% offset x is .002 when y=0. For a parallel line the slope (determined by 4232 in the formula) cannot be changed so the constant must be, from +0.701 to -7.763. A further data set can be created with the altered constant and added to the chart as before:

By observation the offset yield strength (0.2% proof strength) can be seen to be around 80 MPa in this example (where the green line intersects the blue curve).