Least Squares Regression ST. Dev.

[Kris@CEC]

I created a graph and then a trendline to get the slope of the line and then
used m to determine another value which would be constant for all data in
that data set (least squares regression). HOWEVER, I also have to find the
mean and st. dev. for this data. I know the mean is obviously the same at
the constant, but what is the standard deviation and how do i calculate that
off the chart (I'm assuming i do since it seems my only option as i'll always
get 0 in the numerator in the st. dev. function).

 

[Gary''s Student]

If your data is in B1 thru B100, use:

=STDEV(B1:B100)

 

[Kris@CEC]

Sorry, let me explain more:
I have 2 sets of data, A and R (which involves 9 data points- 3 points in
three different intensities). I plot A on the y-axis and B on the x-axis.
Then I make a trend line from the graph. From that line, I take m (the slope
of the line) and use it in an equation that involves both A and R (A is
substituted for m in the equation). From that I get the value of C. C is
the same for all data points, say 0.5. The mean of 9 points of 0.5 is 0.5 -
HOWEVER, the standard deviation for each point is different. I am very
familiar with the standard deviation function and use it on all the other
data sets just fine. How do you get the standard deviation using least
squares regression? I guess that more directly asks my question - sorry to
confuse.

So what i will end up doing is finding the standard deviation of C for each
of these three intensities. Thanks

 

[Mike Middleton]

Kris -

If you're referring to the standard deviation of residuals or "standard
error of estimate," try the STEYX worksheet function.

- Mike
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[Stan Brown]

Thu, 23 Aug 2007 09:46:00 -0700 from Kris@CEC

I created a graph and then a trendline to get the slope of the line and then
used m to determine another value which would be constant for all data in
that data set (least squares regression). HOWEVER, I also have to find the
mean and st. dev. for this data. I know the mean is obviously the same at
the constant,

What you "know" is not true. The mean is generally not equal to the y
intercept.

but what is the standard deviation

See the AVERAGE and STDEV functions.

 

[KKay]

Thanks Stan - appreciate the response - actually in this case that is the
mean. And I've tried the general average and st. dev. functions in excel.
Wouldn't have asked the ? w/o rotting my own brain diving into the help
section first. ;o) ugh.

I've asked my ? on some math forums, hopefully they can help with it. Excel
just may not be able to do with without major human tweeking. Thanks tho!
Love this site, use it all the time.

My problem is that i have to normalize the data from other data to get the
st. dev. and my other problem...i'm in excel 2000. I kept getting errors
with the steyx.