topic Re: T Test in Excel in Statistical Procedures

T Test in Excel

robertrao — Tue, 27 Nov 2012 20:29:43 GMT

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

Dental         A
                   B
                   C
                    D

Regards

Re: T Test in Excel

PaigeMiller — Tue, 27 Nov 2012 21:42:21 GMT

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.

Re: T Test in Excel

robertrao — Tue, 27 Nov 2012 21:59:12 GMT

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

Actually I think Z test and not the T test

Regards

Re: T Test in Excel

Reeza — Tue, 27 Nov 2012 22:34:06 GMT

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

Re: T Test in Excel

robertrao — Tue, 27 Nov 2012 22:41:57 GMT

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

Re: T Test in Excel

PaigeMiller — Wed, 28 Nov 2012 13:36:06 GMT

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.

Re: T Test in Excel

SteveDenham — Wed, 28 Nov 2012 19:26:38 GMT

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

Re: T Test in Excel

SteveDenham — Wed, 28 Nov 2012 19:35:43 GMT

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

Re: T Test in Excel

Reeza — Wed, 28 Nov 2012 21:15:26 GMT

Do the national numbers include your area's number as well, ie is this a subset you're comparing to the total, or is the national some other aggregation of the data?

I'd be slightly concerned about independence assumptions.

Re: T Test in Excel

robertrao — Wed, 28 Nov 2012 21:56:31 GMT

Hi,

For every department we have mean which is compared to the mean of the same department for a hospital of the same kind as ours.

Its basically a survey for nurse satisfaction

The below is for example a survey of 150 centres across the nation for nurse satisfaction

For Caner Center(hypothetical values):

parameter our Mean natioanl Mean STdeviation national no of centers

A 2.4 2.6 0.44 150

B 2.3 2.4 0.32 150

C 2.6 2.8 0.27 150

D 2.2 2.6 0.33 150

E 2.1 2.5 0.31 150

ALso I am not sure if our area is a subset of the national. I think not

Re: T Test in Excel

robertrao — Wed, 28 Nov 2012 21:57:39 GMT

Hi ,

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

Regards

Re: T Test in Excel

robertrao — Thu, 29 Nov 2012 22:20:24 GMT

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

Re: T Test in Excel

SteveDenham — Fri, 30 Nov 2012 13:53:54 GMT

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

Re: T Test in Excel

robertrao — Fri, 30 Nov 2012 14:22:51 GMT

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

Re: T Test in Excel

robertrao — Fri, 30 Nov 2012 14:25:48 GMT

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???

Re: T Test in Excel

SteveDenham — Fri, 30 Nov 2012 14:49:04 GMT

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

Re: T Test in Excel

robertrao — Fri, 30 Nov 2012 15:55:11 GMT

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