regression analysis

[usedtostata]

Hi,

I'm trying to figure out OLS regression with excel. Can anyone tell me why
the data analysis toolpak gives me ANOVA results?

Also, does anyone know an easy method for creating dummy varibles?

Thanks

 

[ShaneDevenshire]

Hi,

I'm afraid I don't know what OLS is, but in the spreadsheet you can look at
the LINEST function or LOGEST. There is a fairly good help on these
functions. You can also get a couple of linear regression lines and formulas
using the chart tool.

By dummy variables do you mean some random data? If so then you have =RAND()
and RANDBETWEEN(1,100) in the spreadsheet and a random number generator in
the Analysis ToolPak.

 

[Jerry W. Lewis]

The ANOVA table evaluates the significance of the model as a whole, vs. the
t-tests below it in the output that evalute the significance of individual
fitted coefficients.

Can you be more specific about your dummy variable question?

Jerry

 

[usedtostata]

Cheers Shane!

OLS stands for ordinary least squares- it's the same thing as linear
regression (LINEST).

I've been through the help thing already and it's pretty useful, but it
doesn't tell you how to deal with categorical variables in regressions.

Dummy variables are the solution to using categorical variables in
regressions- it's when you substitute binary variables for the composites of
categorical variables.

 

[usedtostata]

Hey Jerry, Thanks for your reply.

About the dummies- basically if I want to recode categorical variables into
a series of binary variables, how can I do that?

Those aren't T-tests though. I thought T-tests were for testing the
difference between two means (e.g. mean height for men vs means height for
women).

Cheers.

 

[Mike Middleton]

usedtostata -

Also, does anyone know an easy method for creating dummy varibles? <

For a categorical variable with two values (e.g., male or female), I use one
indicator variable, coded one or zero, indicating presence (e.g., 1 = male)
or absence (e.g., 0 = not male) of that characteristic.

For a categorical variable with k values, I use k-1 indicator variables,
each coded one or zero.

To create the one or zero data, I either (1) type the values in some of the
cells and if there's a repeating pattern I copy large blocks or (2) use an
IF function in each column if the one and zero values depend on values of an
existing categorical variable.

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

 

[Jerry W. Lewis]

'There are more t-tests, Horatio, than are dreamt of in your philosophy'
(apologies to Shakespeare)

If a statistic is normally distributed, then that statistic divided by its
estimated standard error follows the t distribution with degrees of freedom
associated with the standard error estimate. In particular, if you have
independent normally distributed errors, there is a t-test for each OLS
regression coefficient. Those t-tests will rarely be independent of each
other, since the coefficent estimates are usually correlated. Those t-tests
are reported by the ATP regression tool, or can be constructed from LINEST by
dividing a coefficient by its corresponding standard error (given in the 2nd
line of LINEST output).

Jerry