T Test in Excel

[robertrao]

Hi Team,

In an Excel sheet I have the following:

Assuming the SD is the National SD in the formulae How can we do the T-Test for the following table???

Each service has 5 parameters

I am not good in using EXCEL for statistical Test

We have to compare between ours and National

service    Parameters     our Mean      National Mean    national STD    National Units

surgical      A                    

                   B

                   C

                   D

                   E

Dental         A
                   B
                   C
                    D

                   E

Regards

[PaigeMiller]

I'm not sure what t-test you are talking about. Exactly what two means do you want to compare?

It is my opinion that statistical testing is much better done in SAS than Excel. I haven't kept up with the last two versions of Excel, but older versions of Excel could have serious problems with their algorithms to compute SD.

[robertrao]

Our mean variable and the national mean variable.....

Actually I think Z test and not the T test

Regards

[SteveDenham]

Now for a z test, just use the national SD as the population estimate: z = (your mean - national mean)/(national SD).  To get a p value in excel use the formula =1-NORM.S.DIST(z value address,TRUE).

Steve Denham

[robertrao]

Hi,

I have the confusion of when to use 1-NORM.S.DIST(z value address,TRUE). and when not to use the 1-

If my Z values are mix of +ves and negatives.can i still use 1-NORM.S.DIST?????

I read in an article:when the z value is +ve then we have to use 1-NORM.S.DIST(POSITIVE z))

if z-value is negative then we hav to use :NORM.S.DIST(NEGATIVE z)

Please correct me.

Regards

[SteveDenham]

The TRUE option in the test gives the cumulative probability, so if you are interested in a two-tailed test, then you do have to take the absolute value of the Z score, and always use 1-NORM.S.DIST.

I am curious about one thing. Why don't you just do this in SAS?

Steve Denham

[robertrao]

Hi,

Firstly when I put the TRUE option it says you have used too many arguments for this function.

Secondly I have used the formular of

z=(x-m)/(s/sqrt(n))

for a Z of 7.711357 I am getting a p value of 6.21725E-15  (How do I convert to a normal number. When I format the cell it gives me 0.000000

So from your post I need to convert all the negatives to a positive value and use the above formulae//////Could you tell me how to get the absolute value???

LAstly I am new to Using Excel and SAS as well.. I was asked to do it in EXCEL. The requestor wants it in Excel

Regards

[SteveDenham]

Hmm.  When I go to Insert>Formulas>Statistical and click on NORM.S.DIST, I get a dialog box with two arguments labeled Z and Cumulative.  Plugging in Z=1.96 and Cumulative=TRUE, I get a value of 0.975002105.  I know the two-tailed P value for this Z value is 0.05, so, looking down to your next comment, yes, it should be 2*(1-NORM.S.DIST(abs(z), TRUE)).

Regarding the scientific notation--when you format the cell, you will have to include enough decimal places to display your significant digits.

I am still up in the air regarding the division by n in your formula.  If you are doing a Z test, you know the population parameters, which here are the national mean and standard deviation.  I think you should be using:

z=abs((x-m)/s);

However, I am open to discussion on this point.

Steve Denham

[robertrao]

Hi,

Thanks for the detailed explanation.

I noted each points therin.

Regarding the use of the abs((x-m)/s) we also have the national number of units they compared the same 5 parameters. so i was using

abs(x-m) / SD/sqrt(#units)

where SD/sqrt(#units) is basically the Standard Error

And then to the above result I use 2*(1-NORM.S.DIST(abs(z), TRUE)). to get the 2 tailed result

Please let me know if you have any further questions

Regards

[robertrao]

OK to the Absolute value I need to do =ABS(value)

Dont I require to do a 2*1-NORMSDIST(Z) if I am going for a 2 tailed test???

[Reeza]

If you have the mean and SD then you need to hardcode it, ie use the actual formula's underlying the z/t-test

Student's t-test - Wikipedia, the free encyclopedia

[robertrao]

Hi,

Thanks a lot for the help.

I have the mean for the two samples but the SD is available only for the National

Regards

[PaigeMiller]

If you don't have a standard deviation for one of the groups, either you can't do a t-test/z-test, or you have to guess what the standard deviations is.

[SteveDenham]

Have you at all considered NOT doing a test?  Instead, construct a 95% confidence interval on the national mean, since it is the only parameter for which you have variability.  Then examine the interval to see whether the interval contains your mean.  Now this assumes that your mean is measured without error.

So, why don't you have an SD for your own data?  Is it historical data, such that it was never calculated?  Can't you go back to the raw data and calculate an SD?  I am always a little uneasy when a client says they only have the mean.

Steve Denham

[robertrao]

Hi ,

I was asked to assume the SD of the population as the SD

Regards