Comparing two curves in excel 1

[karabiber]

Dear all,
Could you please tell me how I can compare two curves in excel?

I have a master curve from experiment data, I would like to compare this master curve with the one that I get from simulation?

 

[Terrifyingtesttube]

what sort of comparison are you looking for?

your file has experimental and simulation data graphed together, which compares them.

to come up with an idea of how accurately your simulation predicts experimental results, you could try plotting simulation vs experiment when using the same conditions for both. and the closer this plotted line is to y=x, the better your simulation is at figuring out the actual results.

 

[IRstuff]

The normal approach would be to re-run the simulation at the abscissas that you collected the data at, so that the ordinates are directly corresponding. Take the difference and that's your comparison.

TTFN

FAQ731-376

 

[MechEng2005]

I was unable to open the spreadsheet (likely because I'm using Excel 97...). If you can get the points at the same "x" value, then you can subtract at each point as IRstuff said. Otherwise you may need to get a reasonable trendline (either built-in Excel forms or manually determining) to get the equation of each line. You could then use the equation to determine the "y" value at the same "x" coordinate. Or, you could subtract one equation from the other to get an equation for variance. Dividing the variance by experimental would allow for a percentage deviation...

Perhaps if you could explain more clearly what sort of comparison you would like to see, we could be more helpful. As clindeman noted, by having the two sets of data on one graph, it is a comparison.

-- MechEng2005

 

[karabiber]

clindeman:
Thanks for your answer, but what do you mean by saying same conditions?

IRStuff:
Thanks for your answer, Unfortunately it is not possible. Then I should also put the corresponding values

MechEng2005:
Thanks for your answer.
I have a curve, I am attaching it again in 97 format.
I would like see how much deviation do I have in simulation?
Maybe some numbers or something.

In the file, I want to compare TENSION curves.
At the end I would like to get, TENSION curve from simulation deviates .... from that of experiment.

 

[IRstuff]

So, what's the problem? Excel has a myriad of curve fitting and splining functions. Spline the simulation curve, get the interpolated values and take the difference.

TTFN

FAQ731-376

 

[Terrifyingtesttube]

same conditions means that if your experiment varies Temperature and you choose 0, 5, 10, 15 degrees then run your simulation at those same temperatures (in your case it looks like you are using strain). you have already run the experiments, so just plug the experimental strains into the simulation.

your comparison table at the end looks like this:
Strain Exp Sim
0 0.01 0.0092
1 0.02 0.021
2 0.03 0.0299

 

[karabiber]

IRStuff:
The problem is I do not know how to do it. That was why I was asking. Could you please tell me how to do it? It is really important for me.

clindeman:
I am entering Stress and Strain(experiment) data into simulation. I am taking simulation data. So comparison is something that you already have seen. Now I see what you mean. However, the problem is, I do not have the stress values for the same STRAIN values from EXPERIMENT and SIMULATION. If I take the SIMULATION's strain values as reference, then I do not have EXPERIMENT's stress values at the corresponding STRAINs.

 

[IDS]

"So, what's the problem? Excel has a myriad of curve fitting and splining functions. Spline the simulation curve, get the interpolated values and take the difference."

I suppose Excel must have some splining functions, because it will fit a spline to your data, but as far as I know there is no way to access the spline parameters. If you know of a way I'd be very interested to hear it.

An Excel User Defined Function to fit a spline to XY data can be downloaded from:

http://newtonexcelbach.wordpress.com/2009/07/02/cubic-splines/

I hope the instructions in the spreadsheet are self explanatory, but if not, please ask.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[GregLocock]

I vaguely remember that the help explains how to do a polynomial curve fit using LINEST, but it would certainly frighten small children, and it didn't improve my outlook on life either.


Cheers

Greg Locock

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[MechEng2005]

Thanks for re-uploading in historic format...

After looking at the data, the first thing I would do is try to get a better view of the relevant information. The curve/data for compression simulation can be removed since you have no values to compare it to. Similarily, the experimental data with strain greater than about .007 can be removed, since you don't have simulation for comparison. Enlarge the graph a little and the difference between the curves is much easier to see than with all the irrelevant data included. This still does not give you meaningful mathematical comparison, but just makes the curves easier to view.

The other thing is that there are two distinct ranges that exhibit different behavior. This is expected as you are obviously looking at the elastic and plastic regions. I would divide each set of data into two ranges and try to fit a curve to only the range you are interested in. For example, I removed all the data with strain values less than 0.1. Fitting a exponential trendline to the remaining data for each set gave a pretty nice fit.

To add a trendline, right click the data on the graph, and select "Add trendline". A window will appear asking for the type. As I said, I tried an exponential curve. Then, select the "options" tab and put a check mark in "Display equation on chart" and "Display R2 value".

With all of the above steps, I was able to get the following information for strain ranging from ~0.1 to ~0.7:

Experimental: 13.607e^.970x
Simulation: 13.660e^1.023x

("x" indicates the strain)
The R2 value for both curves was <0.998

Using the equations to approximate each curve, I plugged in a test value for strain (0.20) and received stress values that differed by approximatly 0.24. At a strain of 0.68 (at the left side of the chart, where the trendlines are clearly farther apart, I got a difference of about 1.07.

I hope you followed what I have done and it provides some help. If this is not what you are looking for or you need a more accurate or descriptive analysis, I think you will need to find a good statistics book or similar.

-- MechEng2005

 

[karabiber]

I would say that is an engineering approach, and it worked quite well.
Thank you very much. I did it like this!

 

[KiwiMace]

The R-squared value can be directly obtained without curve fitting using the RSQ function in excel.

This is the square of the PEARSON function.