INFORMATION TECHNOLOGY REGRESSION CORRELATION ANALYSIS FOR THE CORRELATION LATTICE IN EXCEL

Array

Authors

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.

Downloads

Published

2019-12-28

How to Cite

Litvinov, A. (2019). INFORMATION TECHNOLOGY REGRESSION CORRELATION ANALYSIS FOR THE CORRELATION LATTICE IN EXCEL: Array. Municipal Economy of Cities, 6(152), 84–88. Retrieved from https://khg.kname.edu.ua/index.php/khg/article/view/5495

Issue

Vol. 6 No. 152 (2019): Series: Engineering science and architecture

Section

статьи

License

The authors who publish in this collection agree with the following terms:
• The authors reserve the right to authorship of their work and give the magazine the right to first publish this work under the terms of license CC BY-NC-ND 4.0 (with the Designation of Authorship - Non-Commercial - Without Derivatives 4.0 International), which allows others to freely distribute the published work with a mandatory reference to the authors of the original work and the first publication of the work in this magazine.
• Authors have the right to make independent extra-exclusive work agreements in the form in which they were published by this magazine (for example, posting work in an electronic repository of an institution or publishing as part of a monograph), provided that the link to the first publication of the work in this journal is maintained. .
• Journal policy allows and encourages the publication of manuscripts on the Internet (for example, in institutions' repositories or on personal websites), both before the publication of this manuscript and during its editorial work, as it contributes to the emergence of productive scientific discussion and positively affects the efficiency and dynamics of the citation of the published work (see The Effect of Open Access).