Using Excel® to Do Statistical Analysis

• Yuri A. W. Shardt

Abstract

This chapter introduces the reader to the application of Excel® to solving statistical problems with an emphasis on regression. Required Excel functions are described, including array functions that allow for the manipulation of matrices in Excel. Writing your own Excel code to extend its functionality and the resulting issues from Excel security are also considered. Finally, the Data Analysis and Solver add-ins are described in detail. Ready-to-use Excel templates are provided for constructing normal probability plots, box-and-whisker plots, and periodograms, as well as linear regression, nonlinear regression, and factorial design analysis. This chapter concludes with three examples: one focusing on linear regression and one on nonlinear regression, and the last one is a collection of factorial design experiments solved using the factorial design template. By the end of this chapter, the reader should be comfortable in using Excel to perform linear regression and factorial design for any level of problem complexity. The reader should also be able to use the appropriate templates and functions to speed up the analysis of a given data set.

Keywords

Nonlinear Regression Excel Spreadsheet Normal Probability Plot Factorial Design Experiment Time Series Plot
These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.

References

1. Elliott JA, Prickett RC, Elmoazzen HY, Porter KR, McGann LE (2007) A multisolute osmotic virial equation for solutions of interest in biology. J Phys Chem B 111:1775–1785
2. Harvey G (2013) Excel® 2013 All-in-One for dummies. Wiley, HobokenGoogle Scholar
3. Jochem M, Körber C (1987) Extended phase diagrams for the ternary solutions H2O − NaCl − glycerol and H2O − NaCl − hydroxyethylstarch (HES) determined by DSC. Cryobiology 24:513–536
4. Prickett RC, Elliott JA, McGann LE (2010) Application of the osmotic virial equation in cyrobiology. Cryobiology 2010:30–42
5. Prickett RC, Elliott JA, McGann LE (2011) Application of the multisolute osmotic virial equation to solutions containing electrolytes. J Phys Chem B 115:14531–14543
6. Ross-Rodriguez LU (2009) Cellular osmotic properties and cellular responses to cooling. University of Alberta, EdmontonGoogle Scholar

© Springer International Publishing Switzerland 2015

Authors and Affiliations

• Yuri A. W. Shardt
• 1
1. 1.Institute of Automation and Complex Systems (AKS)University of Duisburg-EssenDuisbergGermany

Personalised recommendations

Citechapter 