TY - JOUR
AU - Litvinov, A.
PY - 2019/12/28
Y2 - 2024/05/28
TI - INFORMATION TECHNOLOGY REGRESSION CORRELATION ANALYSIS FOR THE CORRELATION LATTICE IN EXCEL: Array
JF - Municipal economy of cities
JA - МЕС
VL - 6
IS - 152
SE -
DO -
UR - https://khg.kname.edu.ua/index.php/khg/article/view/5495
SP - 84-88
AB - <p><em>Regression-correlation analysis has found wide application in communal economy, buildings and statistics, where, unlike, for example, physics, the dependencies between two variables are functional in nature. One value of a variable corresponds to a random value of another variable, which together forms a so-called correlation field. Calculations by regression-correlation analysis are accompanied by a significant number of calculations using complex formulas, which complicates its application. The most affordable application for such calculations is Microsoft Excel, which has a number of built-in functions for this. However, with their help it is possible to process only the samples defined by the coordinates of the points (x<sub>i</sub>, y<sub>i</sub>), which are forming a table with two rows. But in practice, quite often there are problems in which the sample is large enough and the initial data for the regression-correlation analysis are in the form of a correlation lattice. The article proposes an information technology that extends Excel's ability to process samples specified by the correlation lattice. It consists of a number of stages, the results of each are used in the following and are based on the electronic analog of the correlation lattice. Firstly, using the sum (array) function, the frequencies of the variables that are correlated are calculated. To calculate the mean and standard deviation, the sumproduct (array 1, array 2) function with the corresponding modification is used. The most difficult procedure in calculating the covariance and correlation coefficient is to calculate the sum of the pairwise products of the studied variables. An algorithm for its implementation has been developed, and standard Excel tools are used to build plots. To assess the reliability of the regression analysis, the determination coefficient R<sup>2</sup> based on the built-in RSQ(array Y; array X) function was used. An algorithm is developed for using this function as applied to the correlation lattice. The operability of the developed information technology for regression-correlation analysis for the samples defined by the correlation lattice is demonstrated by a numerical example.</em></p>
ER -