Remember Me?

#1
Posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 5
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

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?
#2
Posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 110
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

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?

#3
Posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 5
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

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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Abby Excel Worksheet Functions 2 May 5th 10 01:59 AM Pat Convey Excel Worksheet Functions 3 April 28th 10 01:59 AM MartinW Charts and Charting in Excel 0 March 28th 07 12:22 AM DBane Excel Worksheet Functions 14 September 1st 05 09:54 PM OZDOC1050 Excel Worksheet Functions 1 December 29th 04 02:13 PM

All times are GMT +1. The time now is 05:47 AM.