Simple linear regression question

[FruVirus]

Hi all,

1) I have a set of x data with no noise, a set of y data with noise, and measurement errors associated with the y data. It is known apriori that the fit is linear, y = m*x + b. I wish to calculate the R^2 (i.e. coefficient of determination), residual sum of squares, uncertainty in slope, and uncertainty in intercept for this set of data.

2) I know that the LINEST function can do this relatively easily. However, the LINEST function seems to only work when the y data has no measurement errors.

3) Is there a way to calculate the above mentioned parameters in Excel when there are measurement errors in the y data?

4) Example of data (the linear fit for this data would be y = x + 6 if there were no noise in the y data):

x y Measurement Error in y

0 6.08869 11.2937
1 6.46029 1.82303
2 7.04172 9.16457
3 8.7266 16.0916
4 10 17.6697
5 10.5832 9.8809

[shg]

Welcome to the forum.

How do you know the measurement error? It certainly isn't the difference between the posited relationship and the measurement.

However, the LINEST function seems to only work when the y data has no measurement errors.

If you mean LINEST can only deal with the data it has, that's trivially true. Did you mean something else?

[FruVirus]

1) The data is made up, I was just giving an example (i.e., it does not correspond to anything real).

2) I guess what I mean to ask is: Is there a function in Excel that can calculate the parameters mentioned above if the y data has measurement errors associated with it? For example, Chapter 15 of the third edition of Numerical Recipes in C++ shows how to do this when there are uncertainties associated with each y data measurement. I would like to know if there's also a way to do this in Excel.

[shg]

There is certainly a way. What algorithm do you want to apply?

Have you looked at the extended statistics returned by LINEST when the last argument is TRUE?

[FruVirus]

1) Yes, I have used LINEST to return additional statistical parameters. However, LINEST only accepts known y-data and known x-data as its arguments. I don't believe it can accept measurement errors in y-data.

2) I'm not quite sure what you mean by algorithm...however, I am assuming that the measurement errors are normally distributed.

[shg]

I don't believe it can accept measurement errors in y-data.

I think I'm losing the bubble. You can certainly calculate the chi-square merit function shown in equation 15.2.2 in NRIC (2nd Edition), can you not?

[FruVirus]

Yes, I can calculate the chi-square merit function as shown in Eq. 15.2.2. However, I have to assume that the uncertainty, sigma_i, associated with each measurement y_i is 1. If the uncertainty associated with each measurement y_i is NOT 1, how would I incorporate that in Excel?

[shg]

Why would you have to assume that?

[FruVirus]

The chi-square value that the LINEST function returns assumes an uncertainty of 1 for each y-data point.

[shg]

Please Login or Register  to view this content.

[FruVirus]

1) I was just asking if there was already a built-in function in Excel that takes into account measurement errors in y-data since the values of R^2, chi^2, uncertainty in slope, and uncertainty in intercept would be different than what LINEST returns if there are measurement errors.

2) I have no problem manually entering in those equations in Chapter 15 of the NR book. I just thought I would ask this question to find out once and for all whether or not Excel had such a built-in function.

Anyways, thanks shg for satisfying my curiosity!