turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- Analytics
- /
- Stat Procs
- /
- T Test in Excel

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-27-2012 03:29 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-27-2012 04:42 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-27-2012 04:59 PM

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

Actually I think Z test and not the T test

Regards

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-28-2012 02:35 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-29-2012 05:20 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-30-2012 08:53 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-30-2012 09:22 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-30-2012 09:49 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-30-2012 10:55 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-30-2012 09:25 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-27-2012 05:34 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-27-2012 05:41 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-28-2012 08:36 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-28-2012 02:26 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-28-2012 04:57 PM

Hi ,

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

Regards