Analyzing Data with Regression and ANOVA

This assignment delves into statistical data analysis using regression and ANOVA techniques. Students will analyze provided tables to interpret the coefficients of a regression model, assess its goodness of fit (R-squared), and determine the statistical significance of the relationship between variables. Additionally, they will examine an ANOVA table to identify significant differences in means across different groups.

EXCEL PROJECT

TABLE OF CONTENTS
Question 1........................................................................................................................................3
Question 2........................................................................................................................................4
Question 3........................................................................................................................................5
Question 4......................................................................................................................................12

Table 1 raw calculation for descriptive statistics.........................................................................................5
Table 2: Calculation of median....................................................................................................................5
Table 3: t-Test: Two-Sample Assuming Unequal Variances.......................................................................7
Table 4: F-Test Two-Sample for Variances.................................................................................................8
Table 5: Regression analysis.......................................................................................................................9
Table 6: ANNOVA table.............................................................................................................................9
Table 7: Coefficient table............................................................................................................................9
Table 8: Regression analysis.....................................................................................................................10
Table 9: ANNOVA table...........................................................................................................................10
Table 10: Coefficient table........................................................................................................................10
Table 11: Regression table........................................................................................................................11
Table 12: ANNOVA table.........................................................................................................................11
Table 13: Coefficient table........................................................................................................................11
Table 14: Regression table........................................................................................................................11
Table 15: ANNOVA table.........................................................................................................................12
Table 16: Table of coefficients..................................................................................................................12
Table 17: Regression table........................................................................................................................13
Table 18: ANNOVA table.........................................................................................................................13
Table 19: Table of coefficient....................................................................................................................13
Table 20: Regression table........................................................................................................................14
Table 21: ANNOVA table.........................................................................................................................14
Table 22: Coefficient table........................................................................................................................14
Table 23: Average and variance table........................................................................................................15
Table 24: ANNOVA table.........................................................................................................................15
List of figures

Figure 1 Histogram............................................................................................................................

Question 1
Table 1 raw calculation for descriptive statistics
F Mid value (x) F*X cf X^2 FX^2
10-20 1 15 15 1 225 225
20-30 1 25 25 2 625 625
30-40 10 35 350 12 1225 122500
40-50 16 45 720 28 2025 518400
50-60 11 55 605 39 121 366025
60-70 8 65 520 47 4225 270400
70-80 2 75 150 49 5625 22500
49 2385
F*X 2385
F 49
Mean 48.67
Table 2: Calculation of median
Sum of frequency 49
2
Sum of frequency/2 24.5
L1 40
N/2 24.5
C 12
F 16
Difference between interval 10
Median 47.8

Table 3 Calculation of mode
Mode
F1 10
F0 16
F2 11
L1 40
Mode 43.5
Lower limit + (F1-F0)/[2(F1)-F0-F2)*10
Table 4 Calculation of variance and standard deviation
FX^2 22500
N 80
N-1 79
Variance 22495.48
STDEV 149.98
Variance (S^2) = ∑Fx2 – ((∑Fx)2 /n)/ n – 1
Comparison of mean, median and mode indicate the bell shape curve of histogram. If value
of mean and median is same then it indicate that data is normally distributed. If mean is greater
than median then it means that bell shape curve skewed right. On other hand, if mean is smaller
then median then it means that bell shape curve is skewed left. Mark of 75.5 is exceptional in
Figure 1 Histogram
19/0
1/19
00
29/0
1/19
00
08/0
2/19
00
18/0
2/19
00
28/0
2/19
00
10/0
3/19
00
More
0
2
4
6
8
10
12
14
16
18
0
0.2
0.4
0.6
0.8
1
1.2H is t o g r a m
B in
F re q ue n c y

terms of right tailed test and 25.5 is also exceptional on left tailed test. This is because data
values either comes on right hand or left handed side. Hence, this case is exceptional.
Question 2
Table 3: t-Test: Two-Sample Assuming Unequal Variances
38 47
Mean 51.1 50.05
Variance 171.21 148.6
Observations 10 37
Hypothesized Mean Difference 0
df 14
t Stat 0.23
P(T<=t) one-tail 0.41
t Critical one-tail 1.76
P(T<=t) two-tail 0.82
t Critical two-tail 2.14
Table 4: F-Test Two-Sample for Variances
38 47
Mean 51.1 50.05
Variance 171.21 148.6
Observations 10 37
df 9 36
F 1.15
P(F<=f) one-tail 0.35
F Critical one-tail 2.15
T-test hypothesis
 H0- Mean of final marks not differ significantly between males and females H1- Mean of final marks differ significantly between males and females.

F-test Hypothesis
 Ho- Variances of the two samples not differ significantly from each other H1- Variances of the two samples not differ significantly from each other
Interpretation
 T-test- Value of P is 0.41 which is below alpha value and it can be said that male and
female mean is not different from each other.
 F-test- value of P is 0.35 which is lower than alpha. Hence, null hypothesis is accepted
and it means that two samples do not differ from each other.
Question 3
Table 5: Regression analysis
Regression
Statistics
Multiple
R
0.511313
263
R Square 0.26
Adjusted
R Square 0.25
Standard
Error 1.76
Observati
ons 48
Table 6: ANNOVA table
df SS MS F
Significanc
e F
Regressio
n 1 50.714 50.71 16.28 0

Residual 46 143.27 3.11
Total 47 193.97
Table 7: Coefficient table
Coefficie
nts
Standard
Error t Stat P-value Lower 95%
Upper
95%
Lower
95.0%
Upper
95.0%
Intercept 3.846 0.79 4.88 1.3 2.26 5.43 2.26 5.43
23 0.1 0.03 4.04 0 0.05 0.15 0.05 0.15
It is find out those students that perform excellent in the report give moderate
performance on data analysis. This is concluded because correlation is 0.26 and value of multiple
R is 0.51. Apart from this level of significance 0.00<0.05.
Table 8: Regression analysis
Regression Statistics
Multiple R 0.04
R Square 0
Adjusted R
Square -0.02
Standard Error 5.01
Observations 48
Table 9: ANNOVA table
df SS MS F
Significance
F
Regression 1 1.46 1.46 0.06 0.81
Residual 46 1155.54 25.12
Total 47 1157
Table 10: Coefficient table
Coeffici
ents
Standard
Error t Stat P-value Lower 95%
Upper
95%
Lower
95.0%
Upper
95.0%

Intercept 14.76 2.24 6.6 3.66 10.26 19.26 10.26 19.26
23 -0.02 0.07 -0.24 0.81 -0.16 0.13 -0.16 0.13
It is concluded that students that receive good marks in the report give very low
performance on test. This is concluded because correlation is 0.00 and value of multiple R is
0.03. Value of level of significance is 0.81>0.05 and on this basis it is stated that students
perform good in report but give poor performance in test.
Table 11: Regression table
Regression Statistics
Multiple R 0.04
R Square 0
Adjusted R Square -0.04
Standard Error 5.07
Observations 48
Table 12: ANNOVA table
ANOVA
df SS MS F Significance F
Regression 2 1.741 0.87 0.03 0.97
Residual 45 1155.26 25.67
Total 47 1157
Table 13: Coefficient table
Coefficie
nts
Standard
Error t Stat P-value
Lower
95%
Upper
95%
Lower
95.0%
Upper
95.0%
Intercept 14.589 2.79 5.24 4.18 8.97 20.2 8.98 20.2
23 -0.02 0.09 -0.26 0.8 -0.19 0.15 -0.19 0.15
9 0.04 0.42 0.1 0.92 -0.81 0.9 -0.81 0.9
In this case value of R and R square is 0.00 and 0.03. along with this value of level of
significance is 0.96>0.05 which means that average mark for the reports and the mark for data
analysis are not good predictors for the results of the theory test.

Table 14: Regression table
Regression
Statistics
Multiple
R 0.9
R Square 0.81
Adjusted
R Square 0.81
Standard
Error 5.34
Observati
ons 48
Table 15: ANNOVA table
df SS MS F
Significance
F
Regressio
n 1 5598.4 5598.4 196.36 3.26
Residual 46 1311.51 28.51
Total 47 6909.92
Table 16: Table of coefficients
Coefficie
nts
Standard
Error t Stat P-value Lower 95%
Upper
95%
Lower
95.0%
Upper
95.0%
Intercept 18.61 2.38 7.81
5.70E-
010 13.81 23.4 13.81 23.4
23 1.09 0.08 14.01 3.26 0.93 1.24 0.93 1.24
In present case value of R square is 0.81 followed by R whose value is 0.90. Value of P is
5.7 and on this basis it is stated that Report is the best forecaster of the final mark.
Table 17: Regression table
Regression

Statistics
Multiple
R 0.59
R Square 0.35
Adjusted
R Square 0.33
Standard
Error 9.9
Observati
ons 48
Table 18: ANNOVA table
df SS MS F
Significance
F
Regressio
n 1 2401.03 2401.03
24.49545
41716 1.04
Residual 46 4508.89 98.02
Total 47 6909.92
Table 19: Table of coefficient
Coefficie
nts
Standard
Error t Stat P-value Lower 95%
Upper
95%
Lower
95.0%
Upper
95.0%
Intercept 26.09 5.08 5.14 5.5 15.87 36.31 15.87 36.31
9 3.52 0.71 4.95 1.04 2.09 4.95 2.09 4.95
In this case figure of R square is 0.31 and same of R 0.54. Value of level of significance
is 1>0.05. On the basis of analysis of figures it is assumed that basis it is stated that data analysis
is not the best forecaster of the final mark.
Table 20: Regression table
Regression
Statistics

Multiple
R 0.38
R Square 0.14
Adjusted
R Square 0.12
Standard
Error 11.34
Observati
ons 48
Table 21: ANNOVA table
df SS MS F
Significance
F
Regressio
n 1 992.32 992.32 7.71 0.01
Residual 46 5917.6 128.64
Total 47 6909.92
Table 22: Coefficient table
Coefficie
nts
Standard
Error t Stat P-value Lower 95%
Upper
95%
Lower
95.0%
Upper
95.0%
Intercept 37.011 5.03 7.36 2.6 26.9 47.13 26.9 47.13
6 0.93 0.33 2.78 0.01 0.25 1.59 0.25 1.6
It can be observed that value R square is 0.14 and same of R 0.374. On other hand, level
of significance is 0.00>0.05. It can be concluded that theory is the best forecaster of the final
mark.
Question 4
Table 23: Average and variance table
Count sum Average Variance
R1 49 235 4.8 16.83