стр. 26 |

6.1.D GEOMEAN (вЂњGEOMETRIC MEANвЂќ)

This function is typically used to calculate average growth rate given

compound interest with series rates. In general, the function is good for

estimating average growth or interest rates.

Menu path to function: INSERT/ FUNCTION/ STATISTICAL/

GEOMEAN. Data requirements: All values should be positive.

Figure 88: GEOMEAN (Geometric Mean)

92

Chapter 6: Functions for Basic Statistics

LOCATION MEASURES OF CENTRAL TENDENCY

6.2

(MODE, MEDIAN)

The Median and вЂ” less often вЂ” the Mode are also used for estimating the

central tendency of a series. The Median is much better in situations

where, either:

(a) A few extreme highs or lows are influencing the Mean (note that

the TRIMMEAN or Trimmed Mean function shown in the

previous section can reduce the chance of extreme values over-

influencing a Mean estimate), or

(b) The central tendency is required to obtain the mid-point of

observed values of the data series as in the вЂњMedian VoterвЂќ

models, which are used to know if the вЂњMedian VoterвЂќ threshold is

crossed in support of a point on the nomineeвЂ™s agenda. (In a two-

person face-off, any more than the Median vote will result in a

greater than 50% majority).

Samples will be available at http://www.vjbooks.net/excel/samples.htm.

Figure 89: Some location indicators

Mode

75 th percentile

Median4

(or 3 rd

quartile)

93

Statistical Analysis with Excel

6.2.A MEDIAN

The Median is the number in the middle of a set of numbers. It is the 50th

percentile.

Menu path to function: INSERT/FUNCTION/STATISTICAL/MEDIAN.

Data requirements: Any array/range with real numbers.

6.2.B MODE

This function returns the most frequently occurring value in a range.

Menu path to function: INSERT/FUNCTION/STATISTICAL/MODE.

Data requirements: Any array/range with real numbers. The range has to

contain duplicate data values.

OTHER LOCATION PARAMETERS (MAXIMUM,

6.3

PERCENTILES, QUARTILES, OTHER)

Other useful location indicators for key points in a series are the

quartiles, percentiles, maximum value, minimum value, the Kth largest

value, and the rank.

Samples will be available at http://www.vjbooks.net/excel/samples.htm.

94

Chapter 6: Functions for Basic Statistics

6.3.A QUARTILE

This function calculates a quartile of a data series.

QUARTILE (Data, Quartile)

Choose the quartile you desire to obtain. The five quartiles are shown in

the next table.

Table 11: Choosing the Quartile

Quartile value ofвЂ¦ Calculates the...

0 0.0вЂ¦.1% ile

1 First quartile (25th percentile)

2 Median value (50th percentile)

3 Third quartile (75th percentile)

4 Fourth quartile (99.9x%ile)

стр. 26 |