Excel Regression help?


Excel Regression help?

Author
Message
Gareth Keenan
Junior Member
Junior Member (10 reputation)Junior Member (10 reputation)Junior Member (10 reputation)Junior Member (10 reputation)Junior Member (10 reputation)Junior Member (10 reputation)Junior Member (10 reputation)Junior Member (10 reputation)Junior Member (10 reputation)

Group: Forum Members
Posts: 2, Visits: 1

Here's my post regarding the tools you access in excel by going tools/data analysis/regression. Feel free to chime in if you know something I don't

Am I doing something wrong if my standard errors are all zero, my t Stats are all the same and my F all have the the #NUM! error?

[NEAS: Check if your data points all lie on a straight line.]

Also In the Anova Section (what the heck does Anova mean?) what does the figure directly below the Significance of F represent?

[NEAS: ANOVA = analysis of variance]

If I read properly in my Econometrics book, the F-statistic is testing the Linear relationship between your dependant variable Y and your independent variable(s) X(s).

[NEAS: The F-statistic have several uses.  It tests if all the independent variables (as a group) are significant; if a set of independent variables are significant; if the independent variables have a linear relation, such as the beta for one is equal to the beta for another.  More generally, it tests whether a set of linear relations among the independent variables exists.]

Here's what I did to get to try to better understand this utility. The Ys are dependent, it was a weak off of the top of my head comparison to exam scores and hours studied. By all means, please use this data to understand what I'm talking about, and provide with any additional insight.

Y X
10 390
8 321
3 225
4 300
6 330
5 280

Back to the F-Statistic. When I run this scenario, I get a high F-Stat approx 17.6. To me this suggests a very strong linear relationship between the two variables, which should be the case because that's what I wanted the data to suggest when I made it up. What does my Significance F of .013774281 represent? Is that like the probability there is NOT a linear relationship between the two.

Also, what is the Multiple R or the Standard Error?

The numbers underneath the SS I understand to be the RSS which is directly above the ESS, so that makes sense and then below is the TSS.

What is going on in the MS column. How are the RSS and ESS modified? Why do they no longer sum to the TSS

[NEAS: They are divided by the degrees of freedom]

In any event any and all help/assistance/guidance from the actuarial outpost community would be welcome. Feel free to either explain a concept to me or just point me in the right direction in my Fourth Edition Econometric Models and Economic Forecasts. For example instead of explaining a concept you could say

"R squared, page 72, look it up" and i would be appreciative.

Cheers!

Gareth Keenan.

[NEAS: Use Mahler's Guide to Regression, available on the discussion forum, for clearer explanation of the statistical concepts than in the Econometric Models textbook.]


Lilly Kate
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: Forum Members
Posts: 4, Visits: 1

Gareth, I had the same #NUM problem too.  And I ended up switching to another computer with an older version of excel - 2000 I think and it disappeared.  Never could figure out why that was happening though! 

A question about the discrete change with no dummy variable... I'm not getting a V shaped residual plot like what was expected.  I'm getting a chart with 2 separate lines, both with positive slopes.  I used...

 Y=10-0.25x1 + 0.20(x2)  for x2<10  and

Y=10-0.25x1 + 0.10(x2) for x2>=10

with a sigma of 0.05

Any idea why that would be?? Thanks!!

[NEAS: The cumulative inflation with these equations is 0.2, 0.4, ..., 1.8, 1.0, 1.1, ..., 1.4.  This assumes negative 80% inflation in the tenth year.  It gives two separate lines.]



~Lilly Kate~
ActuaryGirl
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: Forum Members
Posts: 8, Visits: 1

Lilly Kate,

I also had trouble getting a V shape at first.  When running the regression, are you selecting both the x1 column and the x2 column as your X Inputs?  This was my problem - I was only selecting x2 and therefore not seeing the V shape.


Lilly Kate
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: Forum Members
Posts: 4, Visits: 1

Whoops!  Figured out what I did!  I was forcing the inflation to be the same across all years if x2 was >= 10.  Thanks for your help!

 



~Lilly Kate~
GO
Merge Selected
Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...





Reading This Topic


Login
Existing Account
Email Address:


Password:


Social Logins

  • Login with twitter
  • Login with twitter
Select a Forum....











































































































































































































































Neas-Seminars

Search