t-test for single mean (Raw data)
1. The heights of 10 males of a given locality are found to be
70,67,62,68,61,68,70,64,64,66 inches. Is it reasonable to believe that the
average height is 64 inches? Test at 5% of significance.
Step1:Enter the data in excel sheet. Since mean(X) is not given compute X
using the function AVERAGE
Step 2: Since S.D is not given compute S.D using the function STDEVP
Step3:Compute Standard Error by dividing s.d with SQRT (n)
Step4:Form the hypothesis ,Enter alpha value and find degrees of freedom .
Step5:Compute Test Statistic
Step6:Find the table value using the functionTINV(Alpha,D.F)
Step7:Find the table value using the functionTINV(Alpha,D.F) .Since Test
statistic =1.897 < Critical =2.26 we accept Ho There is no significant difference
average height is 64 inches.calculate p value using the function TDist(x,D.F,
Tails)
Step8: p =0.0902 > α=0.05 we accept Ho There is no significant difference
average height is 64 inches.
t- test for Difference of Means
1.A group of 5 patients treated with medicine A weigh 42, 39, 48, 60, 41 kg. A second group
of 7 patients from the same hospital treated with medicine B weigh 38, 42, 56, 64, 68, 69,
62kg. Do you agree with the claim that there is no significant difference in two medicines?
Step 1: Enter the data in MS-Excel sheet as shown below and form
the hypothesis
Step 2: Go to tool-bar and select data as shown below:
Step 3: From Data option select Data analysis as displayed
Step 4: when Data Analysis option is selected, it shows several
options. Then chose t-test: Two-sample Assuming Equal Variances as
displayed below
Step 5 : Having chosen t-Test Two-sample Means, Press ‘Ok’ button.
It shows
Step 6: Enter the range in the box variable 1 Range: A3:A8.
Step 7: Enter the range in the box variable 2 Range: B3:B10.
Step 8: Write the level of significance in the box for Alpha:0.05 and
choose/tick Labels
Step 9: Choose the option Output Range: select the any cell from the
same sheet and enter t-test for two-means then press “Ok” button.
The result of the above steps is in the same sheet output will be
created with name “t-test for two-means”. The sheetcontain the
following.
Step10: tcal=1.703<critical value =2.2281 we accept Ho. there is no significant
difference in two medicines. Since p=0.119>0.05 accept Ho. there is no
significant difference in two medicines.
t-Test for Paired samples
2. An I.Q test was administered to 5 persons before and after they were trained.
The
results are given below :
Candidates : I II III IV V
I.Q before training: 110 120 123 132 125
I.Q after training : 120 118 125 136 121
Test whether there is any change in I.Q. after the training programme. Use 1%
level of significance.
Step 1: Enter the data in MS-Excel sheet as shown below
Step 2: Go to tool-bar and select data as shown below:
Step 3: From Data option select Data analysis as displayed
Step 4: when Data Analysis option is selected, it shows several options. Then
chose t-test: Paired Two Sample for means as displayed below
Step 5 : Having chosen t-Test Two-sample Means, Press ‘Ok’ button. It shows
Step 6: Enter the range in the box variable 1 Range: A4:A9.
Step 7: Enter the range in the box variable 2 Range: B4:B9.
Step 8: Write the level of significance in the box for Alpha: 0.01 and
choose/tick Labels
Step 9: Choose the option Output Range: select the any cell from the same
sheet and enter t-test for two-means then press “Ok” button.
Step10:The result of the above steps is in the same sheet out will be created
with name “t-test for two-means”.
Step11: tcal=0.816<critical value =4.604we accept Ho. there is no significant
difference in IQ levels before and after training. Since p=0.46>0.01 accept Ho.
there is no significant difference in in IQ levels before and after training.
t- test for Sample Correlation Coefficient ‘r’
Problem: Test for significance of correlation for random variables X & Y
X: 12 15 17 19 10 8 6
Y: 25 14 16 13 5 11 9
Step1:Enter the data in MS-Excel sheet . Ho: the coefficient is not significant
i.e., ρ=0 & H1: the coefficient is significant i.e., ρ≠0
Step 2: Go to tool-bar and select data as shown below:
Step 3: From Data option select Data analysis as displayed
Step 4: when Data Analysis option is selected, it shows several options. Then
chose Regression as displayed below
Step 5: Then Press ok button .The following dialogue bow will be opened
Step 6: Enter the range in the input Y Range: A2:A9.
Step 7: Enter the range in the input X Range: B2:B9.
Step 8: Choose/tick Labels
Step 9: Choose the option Output Range: select the any cell from the same
sheet and then press “Ok” button
Step 10: The result of the above steps is:
In the same sheet will be created with name Regression. The sheet contain the
following
Step 11: Read the values of intercept and X from the columns t-stat and P
values
Step 12: Critical Value= TINV (0.05, 5)
Step13:Critical Value= TINV (0.05, 5)=2.571
Step14: Inference :t-stat=0.8365 <Critical=2.571 we accept H o .P=0.427>0.05
we accept Ho. the coefficient is not significant.
Small Sample Tests for difference of variances
F-test
Problem: Two random samples were drawn from two normal populations and
the following results were obtained:
Sample 16 17 18 19 20 21 22 24 26 27
I
Sample 19 22 23 25 26 28 29 30 31 32 35 36
II
Test the equality of population variances.
Step1:Enter the data in excel sheet.
Step 2: Go to tool-bar and select data as shown below:
Step 3: From Data option select Data analysis as displayed
Step 4: when Data Analysis option is selected, it shows several
options. Then chose F-test: Two-sample for Variances as displayed
below
Step5: Having chosen F-Test Two-sample for Variance, Press ‘Ok’
button. It shows
Step 6: Enter the range in the box variable 1 Range: A3:A8.
Step 7: Enter the range in the box variable 2 Range: B3:B10.
Step 8: Write the level of significance in the box for Alpha:0.05 and choose/tick
Labels
Step 9: Choose the option Output Range: select the any cell from the same
sheet and enter F-test Two sample for Variance then press “Ok” button.
Step6:The result of the above steps is:
In the same sheet will be created with name “F-test for Two Variances”. The
sheet contain the following.
Step7:Inference: P=0.165>0.05 so we accept Ho :Population are equal.
Chi –Square Test for Independence of Attributes
Problem: A random sample of students of a university was selected and asked
their opinions about terrorist .The results are given below
Favored Opposed
B.A 120 80
B.com 130 70
B.Sc 70 30
P.G 30 20
Test the hypothesis of opinions are independent of the program of the study.
Step1: Enter the data in excel sheet and form hypothesis
Step2:Compute the excepted frequencies by using the formula E=((Ai)*(Bj))/N
Step3:To compute p-values for chi-square test of independence of
attributes go to formulas→ category: statistical→ CHITEST→ select
observed and expected data range.
Step4:It return p-value which should be compared with l.o.s and make
write the inference.
Step5:Compute the degrees of freedom.
Step6: Find chi-square statistic by using CHINV function.
Step7: Find critical value by using CHINV function.
Step8: Compare chi-square statistic with critical value and write the inference.
Chi –Square Test for Goodness of Fit
Problem: In 120 liters of 5 mice, the numbers of litters which contain 0, 1,
2,3,4,5, females were recorded as under. Fit a binomial distribution to this data
and test for goodness of fit.
No. of female mice (X): 0 1 2 3 4 5
No. of litters (f): 5 20 36 34 22 3
Step1: Enter the data in excel sheet
Step2: Compute the value of fx
fx
Step3: Find mean value =∑ f
Step4: Find P value by using the relation mean =n*p
Step4: Find Q value by using the relation=1-p
Step5: Binomial probabilities are obtained using BINOMDIST function in
statistical category in insert tab. Specify, X-value-values, p, cumulative-
false.
Note: Poisson and normal distributions can be fit using POISSON and
NORMDIST functions
Step6: Find the estimated values .Estimated values =N*P(x)
2
(O−E)
Step7: Compute χ 2=
E
Step 8:Compute chi-square statistic
Step9: To find the critical value use CHIINV (l.o.s, d.f)
Step10: Compare chi-square statistic with critical value and write the inference
