Using Excel^® to Do Statistical Analysis

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.