## 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

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