provides the statistics functionality available in “Analysis ToolPak”

and “Analysis ToolPak VB.” The menu options “Optquest” down till

CB Bootstrap” are linked to the Add-in “Crystal Ball” (not shipped in

the Office CD-ROM).

11 If

too many Add-Ins are loaded, Excel may work too slowly, or even freeze. If you find

this problem occurring, then just load the Add-in when you are going to use it and

unload it before quitting Excel.

164

Chapter 9: Add-ins: Enhancing Excel

Figure 143: The “Data Analysis” menu option

165

Statistical Analysis with Excel

166

Page for Notes

Statistical Analysis with Excel

CHAPTER 10

STATISTICS TOOLS

This chapter discusses the following topics:

” DESCRIPTIVE STATISTICS

” RANK AND PERCENTILE

” BIVARIATE RELATIONS” CORRELATION, COVARIANCE

A proper analysis of data must begin with an analysis of the statistical

attributes of each series in isolation ” univariate analysis. From such an

analysis, you can learn:

” How the values of a series are distributed ” normal, binomial,

etc.

” The central tendency of the values of a series (mean, median,

and mode)

” Dispersion of the values (standard deviation, variance, range,

and quartiles)

” Presence of outliers (extreme values)

168

Chapter 10: Statistics Tools

The answer to these questions illuminates and motivates further, more

complex, analysis. Moreover, failure to conduct univariate analysis may

restrict the usefulness of further procedures (like correlation and

regression). Reason: even if improper/incomplete univariate analysis may

not directly hinder the conducting of more complex procedures, the

interpretation of output from the latter will become difficult (because you

will not have an adequate understanding of how each series behaves).

Note: I do not go into the details of each statistics procedure. For such

details, refer to your statistics textbook or to “SPSS for Beginners”

(available at http://www.vjbooks.net and amazon.com).

This chapter requires the Analysis ToolPak Add-Ins; chapter 9 shows how

to learn how to launch the Add-Ins.

DESCRIPTIVE STATISTICS

10.1

I do not supply the sample data for most of the examples in chapters 36-40.

My experience is that many readers glaze over the examples and do not go

through the difficult step of drawing inferences from a result if the sample

data results are the same as those in the examples in the book.

Choose the menu option TOOLS/DATA ANALYSIS12. The dialog shown

in Figure 144 opens.

If you do not see this option, then use TOOLS / ADD-INS to activate the Add-In for

12

data analysis. Refer to section 41.4.

169

Statistical Analysis with Excel

Figure 144: The options for the menu TOOLS/DATA ANALYSIS

Choose the statistical procedure “Descriptive Statistics.” The dialog for

“Descriptive Statistics” opens. Figure 145 shows this dialog (user-input

form).

Figure 145: Descriptive Statistics dialog