An information technology in Excel when executing an individual calculation task within the course Mathematical statistics

Abstract

The article presents the results of the process of elaborating an information technology which
automatizes the processing of sequences of statistical data (samples) when executing an individually
calculated task (ICT) in Microsoft Office Excel. An ICT plays a special role within the course “Mathematical
Statistics”, and allows using the acquired theoretical knowledge comprehensively during a single process.
Solving problems of mathematical statistics in the context of educational programs for engineering
specialties requires a significant amount of computation, associated with a significant number of
characteristics, complex algorithms for their calculation and graphical interpretation of the results of
solutions. Traditionally, while working on an ICT, students are offered a set of statistical data (sample) with
an unknown distribution law. From this sample, it is necessary to determine the estimate for the
mathematical expectation of the sample data, the estimate for the dispersion and standard deviation,
structural averages, build a histogram of the distribution, suggest a null hypothesis about the type of the
distribution and use the χ2 criterion to accept or reject the hypothesis. In the manual mode, these calculations
are rather time-consuming and monotonous, which requires the use of computer equipment when performing
them. It should be noted that the use of computer technology in the educational literature with respect to the
discipline “Mathematical Statistics” is given very little attention. Within the course “Computer Science”,
only certain functions of calculating statistical characteristics are considered, which complicates the use of
mathematical statistics in the educational process. Excel is one of the most available applications for
students. Therefore, one of the main goals of this article is to develop an information technology in Excel for
consistent calculation of sample characteristics. The proposed technology consists of a number of stages, for
the implementation of which either built-in Excel functions or programmable expressions in spreadsheet
cells are used. Particular attention is paid to building a distribution histogram, suggesting a hypothesis about
the type of the distribution, and verifying statistically this hypothesis. The effectiveness of the technology is
proved by the example provided.