Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to make the average of two Excel graphs?

How to make the average of two Excel graphs?

How to make the average of two Excel graphs?


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


RE: How to make the average of two Excel graphs?

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.


RE: How to make the average of two Excel graphs?

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.

RE: How to make the average of two Excel graphs?

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.

RE: How to make the average of two Excel graphs?

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.


RE: How to make the average of two Excel graphs?

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.


RE: How to make the average of two Excel graphs?

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close