11-27-2012 03:29 PM
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
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.
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).
11-29-2012 05:20 PM
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.
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?
11-30-2012 09:22 AM
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
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
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:
However, I am open to discussion on this point.
11-30-2012 10:55 AM
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
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
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.
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.