Excel: Combining several graphs in one graph + standart deviation

[karabiber]

Hello my friend.
Recently I have Force vs. Displacement data for my polymer. I did 8 experient. I have 8 different data sets like(just an example):

Table1 Table2
x y x y
1 22 1.2 24
2 29 2.6 29
3 33 3.3 24
4 34 3.8 21
I obtained the each individual curve, however, I could not get ONE curve for all these data(averaged values) plus a standard deviation for each point.
The problem is machine takes the measurement at arbitrary points, I mean it does not always take when x is 1, it takes sometimes at 1, sometimes at 1.111 sometimes at 1.2223.

Could you please help me to solve this problem in Excel 2007?
I really appreciate any help.
Thank you for your time.
Regards,
Berk

 

[Clyde38]

How about x-y scatter chart? You can have multiple x and y data on the same graph.

[link

http://www.linkedin.com/in/clydehancock

]

[/url]

 

[magoo2]

I think clyde's suggestion is a good start.

I would add a trendline - probably a polynomial for the example data set or whatever gives you the highest R-squared value for your actual data. Be sure to get the equation results for the trendline along with the R-squared value because this is what you'll be comparing with the other data sets.

So you'll wind up with 8 equations that you can then plot at the same x coordinates. You can then calc the standard deviation for y at this x coordinate. Repeat.

 

[rb1957]

can you solve an over defined set of equations ?

From the data above you have 8 equations, and a cubic (for example) has 4 unknowns. there are matrix methods that will calculate the lest squares best fit to the data ... easy enough to code in excel. from the resulting equation you can calculate the deviations at each point, and so the standard deviation. you could also try different order polynominals to see what works best ...

 

[IRstuff]

I think you're confusing things. The 8 sets of data are all from the same population; they are not different. Therefore, it's perfectly legitimate to combine them all into a single dataset, find the regression curve, and plot the curve and its standard deviation.

TTFN

FAQ731-376

 

[karabiber]

Dear Friends,
Thank you very much for your all helpful posts. It is a little bit late I know.
Unfortunately, I was ill...
Regarding experiment data, I could not find any way to CORRECTLY determine averaged values.
However, I draw 8 x-y scatter chart. and then I took the average of all data, and with this average data, I draw another curve and put it onto the 8 curve graph. It looks kind of average, I know that it is not exactly what I want but I have to move on.
Regarding the standard deviation, no idea. From the Y values, I calculate the standard deviation just to see how big it is. It is ok.

Thanks a lot again.