How to make the average of two Excel graphs?

[Pucp]

Hi, I have TWO different tensile test data (x=elongation, y=stress) from the same material. I want to AVERAGE them and generate just one curve but the main problem for me is that the tensile machine took data at DIFFERENT X (elongation) values in the two tests.

So for example in one test I have this two points(X1=0.104 Y1= 256.3)and (X2= 0.106 Y2= 260.8) and in the other test (for the same region) I have (X1=0.101 Y1=255.1) and (X2=0.105 Y2=260.1) so I can't just make another column and average the x values from the different test.

There is something I can try, find the curve that fits both curves (find 2 formulas) and then generate randomly another column with x values and apply these two formulas to this x column to generate other two colums with y values, then average then and plot the x values (randomly generated) against the y averaged values. But the fitting of my curves is not the best.

So I will really appreciate if there is someone gives me a possible solution in Excel, actually I have 20 different test to work on.

Thanks in advance

Ian

 

[IRstuff]

If you know the form of the equation, you could simply combine the datasets into one and regress on the equation.

I think this is preferrable to averaging, since the variances in the datasets are essentially noise inputs and the minimized mean-squared error should have more precedence.

TTFN

 

[ivymike]

you could try pairing the points (1 from curve A and one from curve B) and calculating the location of the midpoints of the line segments connecting the pairs. You'd have to make sure that the pairings make sense, and that each point is paired with one that is relatively close to it on the x axis. A curve connecting the midpoints should look like it averages the curves A and B.

 

[GeoPaveTraffic]

I would just plot each set of data by hand on a good sheet of graph paper. Then you can use the squares on the graph paper to visually average the two curves at any and all points along the test data curves thereby generating a new curve that is the average of the test data.

If this is something that you will need to do over and over, then working up a spread sheet to intropolate between the data points to predetermined "x" values for each set of test data may make sense. You could then average the data and make a new curve without doing any manual work. However, don't expect the results to be perfect.

 

[SWComposites]

The simplest way to do this is to fit (regress) a curve to the data from all of the specimens. What types of curves have you tried to fit to the data? What is the material? Mil-handbook-5, Section 9.3.2 and Mil-Handbook-17, Volume 1, Section 8.4.5 both have information on fitting and plotting stress-strain data.

Steve

 

[mrgoldthorpe]

How about this, which assumes the strains increase montonically in each test.

Choose the data set that has the greatest range (Set 1).

For each strain value in Set 1 (strn1,i) you know the stress for Set 1 (sig1,i).

Interpolate (using MATCH and INDEX or see the function below which you can insert into a macro) to obtain the stress from Set 2 (sig2,i) corresponding to (strn1,i)

Average the results for (sig1,i) and (sig2,i) for each (strn1,i)

'---------------------------------------------------------
Function Interp(X, Y, Xe)
' This function interpolates and extrapolates linearly on
' arrays of X and Y values
' Input is as follows
' Array of X values that are in a sequence of increasing values
' Array of Y values
' Value Xe for which value of Y is required

' Count number of X values
For Each Item In X
CountX = CountX + 1
Next Item

' Count number of Y values
For Each Item In Y
CountY = CountY + 1
Next Item

If CountX >= 2 Then GoTo 10
' Error not enough values so end calculation
Msg = "Need at least 2 values of X"
Ans = MsgBox(Msg, vbOKOnly)
Interp = "Error"
GoTo 400

10 If CountX = CountY Then GoTo 100
' Error should be equal number of X and Y values
Msg = "Need equal number of X & Y values"
Ans = MsgBox(Msg, vbOKOnly)
Interp = "Error"
GoTo 400

100 If Xe > X(1) Then GoTo 200
' Xe less than smallest X
Interp = Y(2) - ((Y(2) - Y(1)) / (X(2) - X(1)) * (X(2) - Xe))
GoTo 400

200 If Xe < X(CountX) Then GoTo 300
' Xe greater than largest X
Interp = Y(CountX - 1) + ((Y(CountX) - Y(CountX - 1)) / (X(CountX) - X(CountX - 1)) * (Xe - X(CountX - 1)))
GoTo 400

300 For Item = 1 To CountX
If Xe >= X(Item) And Xe <= X(Item + 1) Then GoTo 310
Next Item
310 Interp = (Y(Item + 1) - Y(Item)) / (X(Item + 1) - X(Item)) * (Xe - X(Item)) + Y(Item)

400 '
End Function
'----------------------------------------------------------

Hope this helps.
MRG

http://www.analysis.demon.co.uk

 

[jghrist]

I agree with Steve that the simplest (and best) way is to make a regression using all of the test values. I'd also try fitting two curves, one for each test. If the two tests cover the same range and one is consistently higher than the other, you may want to explain the difference before combining the results into one curve.

I would not generate random points for plotting. This would imply that these were actual test points, which would be misleading.