linest internals

Bruce Bowler

unread,

Apr 16, 2013, 11:08:14 AM4/16/13

to

This probably isn't the right forum to ask in, but are the equations used
by linest to calculate all of the bits that it can output documented
somewhere?

Thanks!
Bruce

Michael Marshall

unread,

Apr 16, 2013, 9:49:26 PM4/16/13

to

The help files on microsoft site does not offer much details about the internal formula used by Linest function.

Linest uses this regression analysis equation http://njinstruments.com/statistics/online-regression-analysis-calculator.html

b = [n ΣXY - ΣX ΣY]/[ n ΣX^2 - [ΣX]^2 )
a = ΣY/n - b ΣX/n

y = a + bx

here b is the slope of the line
and a is the intercept

Statistical software from http://njinstruments.com/ shows detailed workout of the regression analysis as internally used by Linest function

X values
4 5 2 7 3 6 3 2 4 2

Y values
88 95 105 111 103 105 65 42 105 87

Regression Equation = 65.377 + 6.638 X

How Regression Equation was calculated

n #### X #### Y ##### XY ##### X^2
1 #### 4 #### 88 #### 352 #### 16
2 #### 5 #### 95 #### 475 #### 25
3 #### 2 #### 105 ### 210 #### 4
4 #### 7 #### 111 ### 777 #### 49
5 #### 3 #### 103 ### 309 #### 9
6 #### 6 #### 105 ### 630 #### 36
7 #### 3 #### 65 #### 195 #### 9
8 #### 2 #### 42 #### 84 ##### 4
9 #### 4 #### 105 ### 420 #### 16
10 ### 2 #### 87 #### 174 #### 4

#### ΣX = 38 #### ΣY = 906 #### ΣXY = 3626 #### Σ[X2] = 172

b = [n ΣXY - ΣX ΣY]/[ n ΣX^2 - [ΣX]^2 ) #### 6.638
a = ΣY/n - b ΣX/n #### 65.377

Linear Regression Equation #### y = 65.377 + 6.638 X

Stan Brown

unread,

Apr 16, 2013, 10:24:32 PM4/16/13

to

The algorithms are pretty standard. Look up "linear regression" in
any statistics textbook or Web site, such as this one:

http://www.tc3.edu/instruct/sbrown/stat/leastsq.htm#FindShortcut

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...

Bruce Bowler

unread,

Apr 17, 2013, 9:51:18 AM4/17/13

to

Thanks to both Michael and Stan, but that's only 2 of the bits. I should
have said I'm mostly interested in the bits that *aren't* slope and
intercept (they're trivially easy). In particular, I haven't found
anywhere the equations to calculate the SE of the coefficients and the SE
of the constant.

Stan Brown

unread,

Apr 17, 2013, 6:46:33 PM4/17/13

to

Then, with all due respect, you're not looking.

http://www.tc3.edu/instruct/sbrown/stat/infregr.htm
is one of literally millions of examples.

https://www.google.com/search?
hl=en&source=hp&biw=&bih=&q=standard+error+of+slope

Bruce Bowler

unread,

Apr 18, 2013, 8:02:31 AM4/18/13

to

On Wed, 17 Apr 2013 18:46:33 -0400, Stan Brown wrote:

> On 17 Apr 2013 13:51:18 GMT, Bruce Bowler wrote:
>>
>> On Tue, 16 Apr 2013 15:08:14 +0000, Bruce Bowler wrote:
>>
>> > This probably isn't the right forum to ask in, but are the equations
>> > used by linest to calculate all of the bits that it can output
>> > documented somewhere?
>> >
>> > Thanks!
>> > Bruce
>>
>> Thanks to both Michael and Stan, but that's only 2 of the bits. I
>> should have said I'm mostly interested in the bits that *aren't* slope
>> and intercept (they're trivially easy). In particular, I haven't found
>> anywhere the equations to calculate the SE of the coefficients and the
>> SE of the constant.
>
> Then, with all due respect, you're not looking.
>
> http://www.tc3.edu/instruct/sbrown/stat/infregr.htm is one of literally
> millions of examples.
>
> https://www.google.com/search?
> hl=en&source=hp&biw=&bih=&q=standard+error+of+slope

Thank you. I was clearly googling the wrong terms...

Bruce