BookmarkSubscribeRSS Feed
robertrao
Quartz | Level 8


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

16 REPLIES 16
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
robertrao
Quartz | Level 8

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

Actually I think Z test and not the T test

Regards

SteveDenham
Jade | Level 19

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
Quartz | Level 8

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
Jade | Level 19

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
Quartz | Level 8

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
Jade | Level 19

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
Quartz | Level 8

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8


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
Diamond | Level 26

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.

--
Paige Miller
SteveDenham
Jade | Level 19

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
Quartz | Level 8

Hi ,

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

Regards

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 16 replies
  • 2314 views
  • 6 likes
  • 4 in conversation