Finding the CI in t-test analysis

[Guest]

Is there a way to get the confidence interval on the worksheet for t-Test:
Two-Sample Assuming Unequal Variances?

 

[Mike Middleton]

Dahliah2 -

Is there a way to get the confidence interval on the worksheet for t-Test:
Two-Sample Assuming Unequal Variances? <

A method is described on page 132 of my book, Data Analysis Using MS Excel:
Updated for Office XP.

It uses the results from Analysis ToolPak on a worksheet, working
"backwards" from the reported t Stat to get the Standard Error. Use cells
and cell references for the following six quantities:

Observed Mean Difference = Mean2 - Mean1 (using references to cells in the
ToolPak results in the row labeled "Mean")
Standard Error = (Observed Mean Difference - Hypothesized Mean
Difference)/(t Stat)
95% Confidence t =TINV(0.05,df)
Half-Width = (95% Confidence t)*(Standard Error)
Lower Limit = Observed Mean Difference - Half-Width
Upper Limit = Observed Mean Difference + Half-Width

- Mike
www.mikemiddleton.com

 

[Jerry W. Lewis]

The trick with unequal variances is how to calculate standard error and
evaluate df. This is discussed at
http://groups-beta.google.com/group/microsoft.public.excel.misc/msg/65a5754092189c39

This (Satterthwaite's) formula for effective df usually results in
non-integer df. The Analysis ToolPak uses ROUNDDOWN(df,0), which is a
conservative approximation.

The TTEST worksheet function uses the estimated df directly, but that
too takes some work since the TDIST function only uses integer df. t^2
is distributed as F(1,df), but FDIST also only uses integer df. You can
use the identity
FDIST(f,d1,d2) = BETADIST(d2/(d2+d1*f),d2/2,d1/2)
which reduces in this case to
TDIST(t,df) = BETADIST(df/(df+t^2),df/2,0.5)
to reproduce the p-value given by TTEST, which will be smaller than the
p-value given by ATP.

Working backward, to get a confidence interval you would use
TINV(alpha,df) =
SQRT(-BETAINV(alpha,df/2,0.5)*df*(BETAINV(alpha,df/2,0.5)-1))/BETAINV(alpha,df/2,0.5)
to deal with the non-integer df.

Jerry