Statistical Problems Using Excel

This article discusses how to solve statistical problems using Excel with solved examples and explanations. It covers topics like quartiles, standard deviation, coefficient of variation, and more. The article also explains the difference between QUARTILE.INC() and QUARTILE.EXC() functions, and STDEV.P() and STDEV.S() functions. The COVARIANCE function and coefficient of variance are also discussed. References are provided for further reading.

Statistical Problems Using Excel

1 Spreadsheet prepared in excel file.
2.
a. Arithmetic Mean =
∑ Weights
Number of Bags =15 .19 pounds.
Median was the ( 50+1
2 )
th
=25 .5 observation that is the average of 25th and 26th observations.
The value was calculated as 15.2 pounds.
b. The first and the third quartiles were the, ( 50+1
4 )
th
=12. 75th
and
3∗( 50+1
4 )
th
=38 .25th
observations. The values were calculated in MS Excel as 15.00 and 15.40 ponds.
c. Range (0.8 pounds) was the difference between maximum (15.50 pounds) and minimum
(14.70 pounds). Interquartile range (0.4 pounds) was the difference between the third and
first quartiles. Standard deviation was calculated using the formula
σ =∑ ( xi−x
−
)
2
n for
ungrouped data. The value was obtained as 0.24 pounds from MS Excel. Variation was
the square of standard deviation and was calculated as 0.06 pounds in MS Excel (using
standard excel functions). Coefficient of variation
c . v= s
x
− ∗100 %=1 .59 %
from excel sheet.
d. The average weight of a rice bag was 15.19 pounds and median was 15.20 pounds. The
equality of mean and median indicated the normality of the data. There were no outlier
values of the weight of rice bags and hence both measures were appropriate for the data
set. The desired weight was 15.00 pounds, and the company should worry about the
average weight filled in the rice bags.
e. Standard deviation was 0.24 pounds. Hence the interval of mean ± SD was [14.95,
15.43]. As the skewness of the data was zero, signifying that the distribution of weight in
rice bags was normal, the 95% confidence interval was calculated as [15.19 ± 2*0.24] =
[14.52, 15.48]. The population at par mean weight was 15.00, which falls in the 95%
region. Hence the loss of the company, if any, was not statistically significant. The

process was under control. The coefficient of variation was also comparatively small, and
the dispersion of 1.59% in weight of rice in bags was acceptable from practical point of
view.
3.
a. Difference between QUARTILE.INC () and QUARTILE.EXC ()
Both the functions are used to find the values of quartiles. The QUARTILE.INC ()
function calculates quartiles on (n-1) basis whereas QUARTILE.EXC () calculates on
(n+1) basis. The set of formulae for first quartile for the two functions were ( n−1
4 ) th
observation and ( n+1
4 )
th
observation. The QUARTILE.EXC () does not take the median
into calculation and the consequences results in quartile values further from the centre.
The QUARTILE.INC () takes the median into consideration. But due to this, the
Interquartile range narrows, which in turn makes it difficult to point out the outlier
values. The QUARTILE.INC () is equivalent to QUARTILE () function (Liengme,
2015).
b. The STDEV.P () and STDEV.S () are used to find the standard deviation of population
and sample data. STDEV.P () presumes that the entire data is population data and the
calculation is done using ‘n’ method, whereas STDEV.S () takes the ‘n-1’ method. For a
large sample both the formula gives almost same values.
On a similar line, VAR.P () and VAR.S () calculates variance of a population and a
sample based on ‘n’ method
( ∑ ( xi−x
−
) 2
n ) and ‘n-1’ method
( ∑ ( xi−x
−
) 2
n−1 ) .
c. The COVARIANCE function in excel is used to find the covariance between two arrays
of data (integers). COVARIANCE.P () and COVARIANCE.S () calculate the covariance
of population and sample data.
d. To find the coefficient of variance, the mean and standard deviation of the sample data
are calculated using AVERAGE () and STDEV.S () functions. The coefficient of

variance is the ratio between sample mean and sample standard deviation, multiplied by
100 for percentage (Moriarty, Held & Richardson, 2018).
4. Calculation in Excel file.
5. Calculation in Excel file.
References
Moriarty, B., Held, B., & Richardson, T. (2018). Microsoft Excel Functions and Formulas. Stylus
Publishing, LLC.
Liengme, B. (2015). A guide to Microsoft Excel 2013 for scientists and engineers. Academic
Press.