INFORMATION TECHNOLOGY REGRESSION CORRELATION ANALYSIS FOR THE CORRELATION LATTICE IN EXCEL

Keywords: variable, relationship, dependence, regression, correlation, Excel

Abstract

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 (xi, yi), 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 R2 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.

Author Biography

A. Litvinov, O.M. Beketov National University of Urban Economy in Kharkiv

Doctor of Technical Sciences, Professor

References

Savina, G. G. (2016). Practical aspects of evaluating the efficiency of enterprise management of a complex of public utilities. Investment: practice and experience. Kiev, DKS-center, 24, 37 – 41.

Chichulin, V. P. (2015). Correlation analysis of building structures stochastic characteristics. Collection of scientific pa-pers. Series: industrial engineering, construction. Poltava, PoltSTU, 1(43), 81 – 86 .

Zavadsky, I. O. (2011). Excel in core training: a tutorial. Kiev, VHU, 272 p.

Moore, D., & L. R. Weatherford (2004). Decision Modeling in Microsoft Excel . Moscow, Williams.

Voskoboinikov, U. E. , & Tymoshenko, E. I.( 2006). Math-ematical Statistics (with examples in Excel), Novosibirsk, No-vosib. state architecture.-builds university.

Voloshenko, A. B. (2003). Theory of Probability and Math-ematical Statistics: a tutorial, Kiev, KNEU.

Carlberg, C.(2019). Regression analysis in Microsoft Excel, SPb., Alpha book 2019 396 с.

Gmurman, V. E. (2002). Theory of Probability and Mathe-matical Statistics, Moscow, Higher school.

Kendall, M., & Stuart, A. (1973). Statistical Conclusions and Relationships, Moscow, Science.

Dodge, M., & Stinson, C. (2005). Affective work: Mi-crosoft Office Excel, SPb., Piter.

Published
2019-12-28
How to Cite
LitvinovA. (2019). INFORMATION TECHNOLOGY REGRESSION CORRELATION ANALYSIS FOR THE CORRELATION LATTICE IN EXCEL. Municipal Economy of Cities, 6(152), 84-88. Retrieved from https://khg.kname.edu.ua/index.php/khg/article/view/5495