Data Analysis with Microsoft Excel: Updated for Office 2007

Chapter 4 Describing Your Data 157

Table 4-5 Excel functions to calculate the distribution’s center

Function Description

AVERAGE(array) Returns the average or mean of the

values in an array or data range.

GEOMEAN(array) Returns the geometric mean of the values

in an array or data range.

HARMEAN(array) Returns the harmonic mean of the values

in an array or data range.

MEDIAN(array) Returns the median of the values in an

array or data range.

MODE(array) Returns the most frequently occurring

value in an array or data range.

TRIMMEAN(array, percent) Returns the trimmed mean of the values

in array or data range, excluding the

lower and upper values where percent is

the fractional number of data points to

exclude. The function rounds the number

of excluded data points down to the

nearest multiple of 2. If percent = 0.3

and array contains 30 data points, 30

percent of 30 equals 9 and thus 8 points

are excluded: four from the upper range

and four from the lower range.

Now that you’ve learned a little about these functions, use the Univari-

ate Statistics command from the StatPlus add-in to generate a table of their

values.

To create a table of mean and median values:

1 Click Descriptive Statistics from the StatPlus menu and then click

Univariate Statistics.

2 Click the Input button and select Price from the list of range names.

3 Click the Output button, click the New Worksheet option button,

and type Means in the New Worksheet box. Click the OK button.

4 Click the By button and select NE_Sector from the list of range

names.

5 Click the Summary dialog tab.

6 Click the Show all summary statistics checkbox. Figure 4-18 shows

the completed dialog box.