Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula

I have been trying to figure out the leverage formula for a set of X Y
values
X Y
75 16
83 20
85 25
85 27
92 32
97 48
99 48

Using the data analysis add-in and adding residuals then running
regression gives me Predicted Y, Residuals and Standard Residuals, If
my data was in A2:B8 (X and Y) and C2 housed this formula =SLOPE($B
$2:$B$8,$A$2:$A$8)*A2+INTERCEPT($B$2:$B$8,$A$2:$A$ 8) which gives me my
predicted y and D2 housed B2-C2 which gives me my residuals then E2
housed =D2*D2 which gives me my residuals squared. What I am looking
for in cell F2 is HI1(the weight of the leverage value (y value) for
the ith residual). I have the values for the Leverage but do not know
how they were derived.

F2:F8
0.53588
0.200997
0.163787
0.163787
0.180066
0.331229
0.424252

I have the DFITS formula in G2:
=D2*SQRT((7-2-1)/(SUM($E$2:$E$8)*(1-F2)-E2))*SQRT(F2/(1-F2))

But do not know how they derived the Leverage Formula?
Can you please give me some guidance?
Thanks in advance
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 110
Default Regression Leverage Formula (Jerry W. Lewis or Mike Middleton) already have DFITS formula

PJ -

Based on a Google search for "regression leverage" (without the quotes) and
the first item "Leverage and Influential points" at
http://www.stat.rutgers.edu/~saral/p...nfluential.pdf, the
desired values in your column F are equal to the h values shown on pages 17
and 18 of that document.

(An alternative equivalent formula for h is shown on page 12 as h = (1/n) +
p, where p is defined on page 2 of the document.)

For your data set, the variance of x is VARP(A2:A8) =61.42857.

Sum of squared deviations of x is n*VARP(A2:A8) = 430.

Xbar = 88, and for the first observation, x = 75, the deviation is -13 and
the squared deviation is 169.

The intermediate value p = squared deviation divided by sum of squared
deviations, so, for the first observation, p = 169/430 = 0.39302.

For the first observation, h = (1/n) + p = (1/7) + 0.39302 = 0.14286 +
0.39302 = 0.53588.

- Mike
http://www.MikeMiddleton.com



"PJ" wrote in message
...
I have been trying to figure out the leverage formula for a set of X Y
values
X Y
75 16
83 20
85 25
85 27
92 32
97 48
99 48

Using the data analysis add-in and adding residuals then running
regression gives me Predicted Y, Residuals and Standard Residuals, If
my data was in A2:B8 (X and Y) and C2 housed this formula =SLOPE($B
$2:$B$8,$A$2:$A$8)*A2+INTERCEPT($B$2:$B$8,$A$2:$A$ 8) which gives me my
predicted y and D2 housed B2-C2 which gives me my residuals then E2
housed =D2*D2 which gives me my residuals squared. What I am looking
for in cell F2 is HI1(the weight of the leverage value (y value) for
the ith residual). I have the values for the Leverage but do not know
how they were derived.

F2:F8
0.53588
0.200997
0.163787
0.163787
0.180066
0.331229
0.424252

I have the DFITS formula in G2:
=D2*SQRT((7-2-1)/(SUM($E$2:$E$8)*(1-F2)-E2))*SQRT(F2/(1-F2))

But do not know how they derived the Leverage Formula?
Can you please give me some guidance?
Thanks in advance


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula

On Jun 1, 8:06*pm, "Mike Middleton"
wrote:
PJ *-

Based on a Google search for "regression leverage" (without the quotes) and
the first item "Leverage and Influential points" athttp://www.stat.rutgers.edu/~saral/pdf/563/Leverages-influential.pdf, the
desired values in your column F are equal to the h values shown on pages 17
and 18 of that document.

(An alternative equivalent formula for h is shown on page 12 as h = (1/n) +
p, where p is defined on page 2 of the document.)

For your data set, the variance of x is VARP(A2:A8) =61.42857.

Sum of squared deviations of x is n*VARP(A2:A8) = 430.

Xbar = 88, and for the first observation, x = 75, the deviation is -13 and
the squared deviation is 169.

The intermediate value p = squared deviation divided by sum of squared
deviations, so, for the first observation, p = 169/430 = 0.39302.

For the first observation, h = (1/n) + p = (1/7) + 0.39302 = 0.14286 +
0.39302 = 0.53588.

- *Mikehttp://www.MikeMiddleton.com

"PJ" wrote in message

...



I have been trying to figure out the leverage formula for a set of X Y
values
X * * * * * * * *Y
75 16
83 20
85 25
85 27
92 32
97 48
99 48


Using the data analysis add-in and adding residuals then running
regression gives me Predicted Y, Residuals and Standard Residuals, If
my data was in A2:B8 (X and Y) and C2 housed this formula =SLOPE($B
$2:$B$8,$A$2:$A$8)*A2+INTERCEPT($B$2:$B$8,$A$2:$A$ 8) which gives me my
predicted y and D2 housed B2-C2 which gives me my residuals then E2
housed =D2*D2 which gives me my residuals squared. What I am looking
for in cell F2 is HI1(the weight of the leverage value (y value) for
the ith residual). I have the values for the Leverage but do not know
how they were derived.


F2:F8
0.53588
0.200997
0.163787
0.163787
0.180066
0.331229
0.424252


I have the DFITS formula in G2:
=D2*SQRT((7-2-1)/(SUM($E$2:$E$8)*(1-F2)-E2))*SQRT(F2/(1-F2))


But do not know how they derived the Leverage Formula?
Can you please give me some guidance?
Thanks in advance- Hide quoted text -


- Show quoted text -


Mike,

Thank you so much for your guidance and the link. Much appreciated.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Regression Formula Abby Excel Worksheet Functions 2 May 5th 10 01:59 AM
FAO Mike H - Formula Answer provided Pat Convey Excel Worksheet Functions 3 April 28th 10 01:59 AM
To Jerry Lewis or any passing whiz! MartinW Charts and Charting in Excel 0 March 28th 07 12:22 AM
formula for linear regression DBane Excel Worksheet Functions 14 September 1st 05 09:54 PM
Jerry Lewis LUCK ? OZDOC1050 Excel Worksheet Functions 1 December 29th 04 02:13 PM


All times are GMT +1. The time now is 10:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"