Excel Computing Task: Statistical Data Analysis and Interpretation

This document presents a solved Excel computing task involving statistical analysis and interpretation. The task covers a range of statistical concepts, including descriptive statistics, regression analysis, and confidence intervals. It involves analyzing data related to pregnancy tests, country-wise test numbers, and tech support needs. The solution provides detailed calculations, interpretations, and conclusions based on the Excel outputs. Regression analysis is used to explore relationships between variables, and confidence intervals are calculated to assess the reliability of estimates. The document also includes a reflection on a video tutorial about analyzing satisfaction survey data in Excel, highlighting the use of regression lines and related statistical measures. This assignment showcases the application of Excel in statistical analysis and provides insights into interpreting statistical results.

Excel Computing Task

TABLE OF CONTENTS
MAIN BODY...................................................................................................................................3
Question 1...................................................................................................................................3
Question 2...................................................................................................................................4
Question 3...................................................................................................................................5
Question 4...................................................................................................................................7
Question 5...................................................................................................................................8
Question 6...................................................................................................................................8
Question 7...................................................................................................................................9
Question 8.................................................................................................................................10
Question 9.................................................................................................................................12
REFERENCES..............................................................................................................................14

MAIN BODY
Question 1
A
Reality,
pregnant or
not pregnant?
Test
result,
1 or 2?
Mean 1.45 1.57
Standard Error 0.05 0.04976
Median 1 2
Mode 1 2
Standard Deviation 0.5 0.49757
Sample Variance 0.25 0.24758
Kurtosis -1.998737639 -1.9571
Skewness 0.204081633 -0.2871
Range 1 1
Minimum 1 1
Maximum 2 2
Sum 145 157
Count 100 100
Confidence
Level(95.0%) 0.099210845 0.09873
B
By using the above analysis it is clear that the relationship between the two variables is
the difference between the sample means. This is particularly because of the reason that both the
variable that is reality and test result are wide (Black, 2019). Thus, because of this reason the
difference between the sample means is correct for the present study.
C
Reality,
pregnant or
not pregnant?
Test
result, 1
or 2?
Mean 1.53 1.45
Standard Error 0.050161356 0.05
Median 2 1

Mode 2 1
Standard Deviation 0.501613558 0.5
Sample Variance 0.251616162 0.25
Kurtosis -2.026035575 -1.99874
Skewness -0.122055094 0.204082
Range 1 1
Minimum 1 1
Maximum 2 2
Sum 153 145
Count 100 100
Confidence
Level(95.0%) 0.09953101 0.099211
D
With the above output it is clear that the relation between the variables involves the
analysis of two variables that is the difference between the sample mean is being undertaken.
The reason underlying this fact is that the descriptive statistics is being calculated and in this
only the difference in sample mean can be included.
E
Out of both the version, the test which is better is the version 1 because in this case that
standard deviation and mean are less and this provides for a good and wider view to the
company.
Question 2
A
which country
Number
of tests
Mean 1.5 2831.21
Standard Error 0.050251891 139.317
Median 1.5 2528
Mode 1 1323
Standard Deviation 0.502518908 1393.17
Sample Variance 0.252525253 1940923
Kurtosis -2.041237113 -1.67112
Skewness -6.86594E-18 0.169977
Range 1 3985
Minimum 1 1011

Maximum 2 4996
Sum 150 283121
Count 100 100
Confidence
Level(95.0%) 0.099710651 276.4352
B
With the analysis of the above the relationship is the difference between the sample mean
as the use of descriptive statistics is undertaken. In this the correlation coefficient is not included
and also not the sample proportion is not included.
C
D
In case the quantitative variable is normally distributed for both the group then in that
case the above graph states that the number of test is different in both the countries and as a
result of this number of test changes with respect to the country (Skordi and Fraser, 2019).
Question 3
A
Number of
tests
Number
of people
needing
tech
support
Mean 2831.21 179.82
Standard Error 139.317003 3.93845

Median 2528 179
Mode 1323 122
Standard Deviation 1393.17003 39.3845
Sample Variance 1940922.733 1551.139
Kurtosis -1.671123192 -0.55609
Skewness 0.169977254 -0.0738
Range 3985 168
Minimum 1011 92
Maximum 4996 260
Sum 283121 17982
Count 100 100
Confidence
Level(95.0%) 276.4351519 7.814739
B
By evaluating the graph it is clear that there is a two linear relation between the variables.
The reason pertaining to the fact is that in case the number of test and the number of people
requiring the tech support is having linear relation as there is a straight line relation between the
two variables.
C
Number of
tests
Number
of people
needing
tech
support

Mean 1517.12 152.8
Standard Error 39.19346718 4.049591
Median 1546.5 160.5
Mode 1323 122
Standard Deviation 277.1396642 28.63493
Sample Variance 76806.39347 819.9592
Kurtosis -1.134922608 -0.91209
Skewness -0.227668113 -0.3384
Range 949 110
Minimum 1011 92
Maximum 1960 202
Sum 75856 7640
Count 50 50
Confidence
Level(95.0%) 78.76221962 8.137957
D
The relation between the variable is defined by the difference within the sample means
(Gupta, 2019). This is being undertaken because of the reason that in this the use of sample mean
is undertaken and this assist in finding out the deviation within the mean of both the variables.
E
Y= 1.1989 x + 1486.5
F
Y= 1.1989 x + 1486.5
Y= 1.1989 (1000) + 1486.5
Y= 1989 + 1486.5
Y= 3475.5
Question 4
A
i
The estimate of the population mean number of tests for country A is 1517.12
ii
The standard error for this estimate is approximately 39.19.
B

i
The estimate relating to mean number of test for country B is 4145.3
ii
Further the standard error relating to this estimate is 80.06.
Question 5
A
The confidence interval for proportion of pregnant women that test positive is
B
The confidence interval for version 2 for the proportion of pregnant women test positive is
Question 6
A
Regression Statistics
Multiple R 0.55420877
R Square 0.307147361
Adjusted R
Square 0.300077436
Standard Error 0.416273779
Observations 100
ANOVA
df SS MS F
Significance
F
Regression 1 7.528182 7.528182 43.44422 2.21E-09
Residual 98 16.98182 0.173284
Total 99 24.51

Coefficients
Standard
Error t Stat
P-
value
Lower
95%
Upper
95%
Lower
95.0%
Upper
95.0%
Intercept 2.36969697 0.12827 18.47428
1.08E-
33 2.115149 2.624245 2.115149 2.624245
Reality,
pregnant or
not pregnant?
-
0.551515152 0.083674 -6.59122
2.21E-
09 -0.71756 -0.38547 -0.71756 -0.38547
B
The confidence interval is being defined as the probability that whether the parameter
will fall between pair of values around the mean or not (Nielsen, 2018). In the present case of the
analysing relation between variable reality, pregnant or not pregnant and test result the
confidence interval taken is the 95%. Thus, with the above regression it is clear that there is not
any significant value being present within both the variables.
C
The p value of the present regression is 2.21 and this is far more than the standard that
0.05. Thus, it can be implied that the test result that is positive or negative does not depend on
the reality that is pregnant or not pregnant.
Question 7
A
Regression Statistics
Multiple R 0.9479892
R Square 0.898683524
Adjusted R
Square 0.897649682
Standard Error 445.7062474
Observations 100
df SS MS F
Significance
F
Regression 1 1.73E+08 1.73E+08 869.2662 1.61E-50
Residual 98 19468098 198654.1
Total 99 1.92E+08
Coefficients Standard t Stat P- Lower Upper Lower Upper

Error value 95% 95% 95.0% 95.0%
Intercept -1111.06 140.9447 -7.88295
4.5E-
12 -1390.76 -831.36 -1390.76 -831.36
which
country 2628.18 89.14125 29.48332
1.61E-
50 2451.282 2805.078 2451.282 2805.078
B
The confidence interval is the limit at which the data is being tested. In the present case
the test variable included the country and the number of test (Laugerman and Saunders, 2019). In
the present case the confidence interval that is the level at which hypothesis is tested is 95 % that
is p value must be less than 0.05.
C
But in the present case the p value is more than the standard that is 0.05. Thus, it can be
implied that there is not any significant relation being present between both the variables.
Question 8
A
Regression Statistics
Multiple R 0.864651627
R Square 0.747622436
Adjusted R
Square 0.745047154
Standard Error 19.88635964
Observations 100
df SS MS F
Significance
F
Regression 1 114807 114807 290.3071 4.65E-31
Residual 98 38755.8 395.4673
Total 99 153562.8
Coefficients
Standard
Error t Stat P-value
Lower
95%
Upper
95%
Lower
95.0%
Upper
95.0%
Intercept 110.6154769 4.522379 24.45958
1.63E-
43 101.641 119.59 101.641 119.59
Number of
tests 0.024443444 0.001435 17.0384
4.65E-
31 0.021597 0.02729 0.021597 0.02729

B
Regression Statistics
Multiple R 0.961030332
R Square 0.9235793
Adjusted R
Square 0.921987202
Standard Error 7.99795664
Observations 50
df SS MS F
Significance
F
Regression 1 37107.57 37107.57 580.1021 1.87E-28
Residual 48 3070.431 63.96731
Total 49 40178
Coefficients
Standard
Error t Stat P-value
Lower
95%
Upper
95%
Lower
95.0%
Upper
95.0%
Intercept 2.155090915 6.356088 0.339059 0.736044 -10.6247 14.93486 -10.6247 14.93486
Number of
tests 0.099296634 0.004123 24.08531 1.87E-28 0.091007 0.107586 0.091007 0.107586
C
The standard error is less in case of output from part b.
D
In both the cases the hypothesis was to claim that whether there is relation present
between number of test and number of people needing tech support. But in the present case, the
significance value was more than the standard that is more than 0.05. Thus, in both the situation
the relation is not present. But in case of part b there can be some of the relation being found.
This is because of the reason that in this case the significance value is less as compared to the
first case (Wang and et.al., 2018). Also, the correlation between both the variables only in case
of country A is more that is 96%. This implies that there is high correlation between both the
variables and as one variable will be change the another one will also change. Also the R square
implies that it is 92 % which states that change in the independent variable will cause a 92%
change in the independent variable.
E

The confidence interval of part B is also tested at 95 % and with the help of confidence
interval it can be checked that whether there is a relation being present in the variables or not. In
the present case the significance value is more than the standard and because of this, there is not
any relation being present in the variables.
F
The p value is the case of part b is 1.87E-28 which is more than that standard that is 0.05.
Thus, this simply states that there is not any relation being present in the variables tested.
Question 9
The video selected is the “how to analyze satisfaction survey data in Excel with help of Countif”.
The question was provided to analyse the relation between the hours spent studying and caffeine
consumption among the student at their school. For the solution the use of regression line was
undertaken. For this graph was prepared keeping caffeine at X axis and time studying at Y axis
and after that the data was placed on the graph (Taylor, 2018). Further the R sq was explained
that is denotes the change within the independent variable causing the change in the dependent
variable. The S is the standard deviation which outlines the fact that how much the variable
deviates from the regression line. The coef denotes the slope of regression line and SE Coef
denotes the standard error within the regression line. In order to calculate the confidence interval
all these element are necessary and the video explained it in better and effective manner.

REFERENCES
Books and Journals
Black, K., 2019. Business statistics: for contemporary decision making. John Wiley & Sons.
Gupta, S. C., 2019. Business statistics. Himalaya.
Laugerman, M. R. and Saunders, K. P., 2019. Supporting student learning through instructional
videos in business statistics. Decision Sciences Journal of Innovative Education. 17(4).
pp.387-404.
Nielsen, P. B., 2018. The puzzle of measuring global value chains–The business statistics
perspective. International economics. 153. pp.69-79.
Skordi, P. and Fraser, B. J., 2019. Validity and use of the What Is Happening In this Class?
(WIHIC) questionnaire in university business statistics classrooms. Learning
Environments Research. 22(2). pp.275-295.
Taylor, S., 2018. Business statistics: for non-mathematicians. Bloomsbury Publishing.
Wang, P., and et.al., 2018. Examining undergraduate students’ attitudes toward business
statistics in the United States and China. Decision Sciences Journal of Innovative
Education. 16(3). pp.197-216.