Comparing 2 series of data

[Tobi Wong]

Hello,
I have a question regarding scatter (XY) charts on Excel
2000. I have 2 series of data that I want to compare
with one another. I have plotted both series of data on
a scatter (XY) chart in order to compare the 2 series in
terms of the relationship between variables, X and Y. In
addition, I have included a 2nd order polynomial
trendline for each series so that the graph contains 2
trendlines (one for each data series). I want to know if
there's a way for me to compare these 2 series of data
(possibly using slopes of the trendlines) and obtain p
values and confidence intervals to know if there is a
significant difference between the 2.

Another problem I'm having: I have a series of data and
I want to know if 2 variables within that series
correlate with one another. In other words, I want to
know if Y increases/decreases as X increases/decreases
and vice versa. I have plotted out the data and added a
linear trendline. I was thinking that the slope of the
trendline may indicate a correlation, but I don't know
how to determine if the correlation is significant.
Any help would be greatly appreciated. Thank you very
much.

 

[Jon Peltier]

Tobi -

Several years ago I put together a workbook that did this type of test.
You pasted two sets of points into the worksheet, it computed the
fitting coefficients (I only used a first order linear fit). Then it
performed tests on these coefficients. If memory serves, it was a T
test on slope, a T test on intercept, and an F test on a more
complicated multiple regression to test for coincidence of the lines.

You should consult a statistics textbook for the appropriate formulas.
Tests for equal slope and intercept, and tests for coincidence of two
lines, should be covered in a decent text.

I think if you have the same number of points, and the X values all
match up, you could do a paired T test on the Y values. This ignores
the issues inherent in fitting data to arbitrary polynomials, and is
mathematically easier.

- Jon