Correlating data to a given curve 2

peglor · Oct 12, 2002

Has Excel a built in function to calculate the level of correlation between a set of data points and a given equation or other set of points?

Since the trendline function for the graphs will already give the correlation coefficient between a given set of data points and a best fit trend line generated by the program for these points.

Is there a way of using a different line to the trendline for the same calculation?

DeltaCascade · Oct 12, 2002

?You should be able to calculate it yourself, quite easily. Recall the derivation of a least squares fit curve using calculus. Just add up the square of the difference between each data point and what it "should be" (the other line or set of data points that you mentioned?) to get the sum of the square of errors and correlate from there?... it can be done manually once you have the definitions of what you are looking for. I doubt is is built in .. however, defining what you need and programming it into Excel shouldnt be that tough?
Good luck...

IRstuff · Oct 12, 2002

One thing that should be carefully considered is potentially improper application of least squares regression.

The basic premise is equal weighting of all errors. This may lead to large errors if the data is highly non-linear or if there is a large dynamic range in the data. Linearization prior to fitting may prove to be more useful.

TTFN

melone · Oct 12, 2002

try the CORREL function...

peglor · Oct 17, 2002

Thanks melone - that's exactly what I was looking for [spin]

It works in StarOffice anyway, that's all I have access to at the moment.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Correlating data to a given curve 2

peglor

Mechanical

DeltaCascade

Chemical

IRstuff

Aerospace

melone

Electrical

peglor

Mechanical

Similar threads

Part and Inventory Search

Sponsor