How to make the average of two Excel graphs?
How to make the average of two Excel graphs?
(OP)
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





RE: How to make the average of two Excel graphs?
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
RE: How to make the average of two Excel graphs?
RE: How to make the average of two Excel graphs?
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.
RE: How to make the average of two Excel graphs?
Steve
RE: How to make the average of two Excel graphs?
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
RE: How to make the average of two Excel graphs?
I would not generate random points for plotting. This would imply that these were actual test points, which would be misleading.