{"id":1386,"date":"2023-06-08T19:48:26","date_gmt":"2023-06-08T19:48:26","guid":{"rendered":"https:\/\/kpwalters.com\/?page_id=1386"},"modified":"2023-08-12T01:31:05","modified_gmt":"2023-08-12T01:31:05","slug":"some-stat-funcs-excel","status":"publish","type":"page","link":"https:\/\/kpwalters.com\/index.php\/other-projects\/some-stat-funcs-excel\/","title":{"rendered":"Some stat functions in Excel"},"content":{"rendered":"\n
\n
Some stat functions in Excel<\/h5><\/div>\n<\/div>\n\n\n\n
\n
\n

Last update June 8, 2023<\/em><\/p>\n\n\n\n

Excel file for download<\/a>
Variance and standard deviation<\/a>
Covariance<\/a>
Correlation<\/a>
Charts<\/a>
Coefficient of determination<\/a><\/p>\n<\/div>\n\n\n\n

\n
Overview<\/h5><\/div>\n\n\n\n

I created this workbook is just for fun when enrolled in a bootcamp program. It helped me better understand some of the standard statistical functions used in Excel, notably COVAR, STDEV, CORREL, and RSQL.<\/p>\n\n\n\n

In the workbook, I calculated variance, covariance, standard deviation, correlation, and the coefficient of determination (\u201cr-squared\u201d) by using the standard statistical formulas rather than the built-in functions in Excel.<\/p>\n\n\n\n

I include this workbook in my portfolio in the event that some other student of statistics finds it useful or perhaps inspires them to do their own base computations. It also demonstrates\u2014admirably in my opinion\u2014useful application of Named Ranges.<\/p>\n\n\n\n

Excel file<\/h5><\/div>\n\n\n\n

Click here<\/a> to download the Excel file.<\/p>\n\n\n\n

First worksheet:
Variance and Standard Deviation<\/h5><\/div>\n\n\n\n

The first worksheet features two samples, identified simply as x<\/em> (A2:A31) and y<\/em> ( B2:B31). Both are also Named Ranges. There are several formulas which demonstrate how to calculate variance and standard deviation of the samples with and without using Excel\u2019s VAR and STDEV functions.<\/p>\n\n\n\n

Second worksheet: Covariance<\/h5><\/div>\n\n\n\n

The second worksheet shows the calculation of covariance between an independent variable (A2:A31) and a dependent variable (B2:B31), appropriately labeled x<\/em> and y<\/em>. There are a variety of calculations, all arriving at the same results, with and without Excel\u2019s COVAR functions. The worksheet demonstrates the use of Named Ranges pretty extensively.<\/p>\n\n\n\n

Third worksheet:
Correlation<\/h5><\/div>\n\n\n\n

The third worksheet shows the calculation of correlation between an independent variable (A2:A31) and a dependent variable (B2:B31), appropriately labeled x<\/em> and y<\/em>. There is one calculation without Excel\u2019s CORREL function and one with.<\/p>\n\n\n\n

Fourth worksheet: Charts<\/h5><\/div>\n\n\n\n

In the fourth worksheet, I created several sets of dependent variables based on the single set of independent variables found in cells A2:A31. I then graphed them and compared the values of covariance and correlation among the different sets.<\/p>\n\n\n\n

The worksheet includes two charts that show the limitations of using correlation on a polynomial. <\/p>\n\n\n\n

Fifth worksheet:
Coefficient of determination<\/h5><\/div>\n\n\n\n

The last worksheet shows how to calculate the coefficient of determination (\u201cr-squared\u201d) where the dependent variable has a polynomial relationship to the independent variable.<\/p>\n\n\n\n

It is notable in that Excel\u2019s RSQ function does not properly calculate the coefficient of determination for a polynomial relationship. However, we can still use built-in functionality (rather than calculating r-squared \u201cmanually\u201d as in cell I15) by pulling the r-squared value from LINEST.<\/p>\n<\/div>\n\n\n\n

\n

Excel filesExcel<\/em><\/p>\n<\/div>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"

Some stat functions in Excel Last update June 8, 2023 Excel file for downloadVariance and standard deviationCovarianceCorrelationChartsCoefficient of determination Overview I created this workbook is just for fun when enrolled in a bootcamp program. It helped me better understand some of the standard statistical functions used in Excel, notably COVAR, STDEV, CORREL, and RSQL. In …<\/p>\n

Some stat functions in Excel<\/span> Read More »<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":933,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"page-builder","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"disabled","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"enabled","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"kevin.p.walters@live.com","author_link":"https:\/\/kpwalters.com\/index.php\/author\/kevin-p-walterslive-com\/"},"uagb_comment_info":0,"uagb_excerpt":"Some stat functions in Excel Last update June 8, 2023 Excel file for downloadVariance and standard deviationCovarianceCorrelationChartsCoefficient of determination Overview I created this workbook is just for fun when enrolled in a bootcamp program. It helped me better understand some of the standard statistical functions used in Excel, notably COVAR, STDEV, CORREL, and RSQL. In…","_links":{"self":[{"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages\/1386"}],"collection":[{"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/comments?post=1386"}],"version-history":[{"count":20,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages\/1386\/revisions"}],"predecessor-version":[{"id":1703,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages\/1386\/revisions\/1703"}],"up":[{"embeddable":true,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages\/933"}],"wp:attachment":[{"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/media?parent=1386"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}