We consider a random variable x and a data set S = {x₁, x₂, …, x_n} of size n which contains possible values of x. The data set can represent either the population being studied or a sample drawn from the population.

We seek a single measure (i.e. a statistic) which somehow represents the center of the entire data set S. The commonly used measures of central tendency are the mean, median and mode. Besides the normally studied mean (also called the arithmetic mean) we also consider two other types of mean: the geometric mean and the harmonic mean.

Excel Functions: If R is an Excel range which contains the data elements in S then the Excel formula which calculates each of these statistics is shown in Figure 1.

Figure 1 – Measures of central tendency

Observation: All these functions ignore any empty or non-numeric cells.

While formulas such as AVERAGE(R1) (as well as VAR(R), STDEV(R), etc. described on other webpages) ignore any empty or non-numeric cells, they return an error value if R contains an error value such as #NUM or #DIV/0!. This limitation can often be overcome by using the following approach:

=AVERAGE(IF(ISERROR(R),””,R))

This array formula returns the mean of all the cells in R1 ignoring any cells that contain an error value. Since this is an array formula, you must press Ctrl-Shft-Enter. An alternative approach is to use the following function.

Real Statistics Function: The Real Statistics Resource Pack provides the following array function:

DELErr(R) = the array of the same size and shape as R consisting of all the elements in R where any cells with an error value are replaced by a blank (i.e. an empty cell).

E.g. to find the average of a range R which may contain error cells, you can use the formula

=AVERAGE(DELErr(R))

Real Statistics Data Analysis Tool: The Remove error cells option of the Reformatting a Data Range data analysis tool described in Reformatting Tools makes a copy of the inputted range where all cells that contain error values are replaced by empty cells.

To use this capability, press Ctrl-m and double click on Reformatting a Data Range. When the dialog box shown in Figure 2 of Reformatting Tools, fill in the Input Range, choose the Remove error cells option and leave the # of Rows and # of Columns fields blank. The output will have the same size and shape as the input range.

Mean

We begin with the most commonly used measure of central tendency, the mean.

Definition 1: The mean (also called the arithmetic mean) of the data set S is defined by

Excel Function: The mean is calculated in Excel using the function AVERAGE.

Example 1: The mean of S = {5, 2, -1, 3, 7, 5, 0, 2} is (2 + 5 – 1 + 3 + 7 + 5 + 0 + 2) / 8 = 2.875. We achieve the same result by using the formula =AVERAGE(C3:C10) in Figure 2.

Figure 2 – Excel examples of central tendency

Observation: When the data set S is a population the Greek letter µ is used for the mean. When S is a sample, then the symbol x̄ is used.

Observation: When data is expressed in the form of frequency tables then the following property is useful.

Property 1: If x̄ is the mean of sample {x₁, x₂, …, x_m} and ȳ is the mean of sample  {y₁, y₂, …, y_n} then the mean of the combined sample is

Similarly, if µ_x is the mean of population {x₁, x₂, …, x_m}  and µ_y is the mean of population {y₁, y₂, …, y_n} then the mean of the combined population is

Real Statistics Functions: The Real Statistics Resource Pack furnishes the following array functions:

COUNTCOL(R1) = a row range which contains the number of numeric elements in each of the columns in R1

SUMCOL(R1) = a row range which contains the sums of each of the columns in R1

MEANCOL(R1) = a row range which contains the means of each of the columns in R1

COUNTROW(R1) = a column range which contains the number of numeric elements in each of the rows in R1

SUMROW(R1) = a column range which contains the sums of each of the rows in R1

MEANROW(R1) = a column range which contains the means of each of the rows in R1

Example 2: Use the COUNTCOL and MEANCOL functions to calculate the number of cells in each of the three columns in the range L4:N11 of Figure 3 as well as their means.

Figure 3 – Count, Sum and Mean by Column

The array formula =COUNTCOL(L4:N11) produces the first result (in range L13:N13), while the formula =MEANCOL(L4:N11) produces the second result (in range L14:N14) and the formula =SUMCOL(L4:N11) produces the third result (in range L15:N15).

Remember that after entering any of these formulas you must press Ctrl-Shft-Enter.

Median

Definition 2: The median of the data set S is the middle value in S. If you arrange the data in increasing order the middle value is the median. When S has an even number of elements there are two such values; the average of these two values is the median.

Excel Function: The median is calculated in Excel using the function MEDIAN.

Example 3: The median of S = {5, 2, -1, 3, 7, 5, 0} is 3 since 3 is the middle value (i.e the 4th of 7 values) in -1, 0, 2, 3, 5, 5, 7. We achieve the same result by using the formula =MEDIAN(B3:B10) in Figure 2.

Note that each of the functions in Figure 2 ignores any non-numeric values, including blanks. Thus the value obtained for =MEDIAN(B3:B10) is the same as that for =MEDIAN(B3:B9).

The median of S = {5, 2, -1, 3, 7, 5, 0, 2} is 2.5 since 2.5 is the average of the two middle value 2 and 3 of -1, 0, 2, 2, 3, 5, 5, 7. This is the same result as =MEDIAN(C3:C10) in Figure 2.

Mode

Definition 3: The mode of the data set S is the value of the data element that occurs most often.

Example 4: The mode of S = {5, 2, -1, 3, 7, 5, 0} is 5 since 5 occurs twice, more than any other data element. This is the result we obtain from the formula =MODE(B3:B10) in Figure 2. When there is only one mode, as in this example, we say that S is unimodal.

If S = {5, 2, -1, 3, 7, 5, 0, 2}, the mode of S consists of both 2 and 5 since they each occur twice, more than any other data element. When there are two modes, as in this case, we say that S is bimodal.

Excel Function: The mode is calculated in Excel by the formula MODE. If range R contains unimodal data then MODE(R) returns this unique mode. For the first data set in Example 3 this is 5. When R contains data with more than one mode, MODE(R) returns the first of these modes. For the second data set in Example 4 this is 5 (since 5 occurs before 2, the other mode, in the data set). Thus MODE(C3:C10) = 5.

As remarked above, if there is more than one mode, MODE returns only the first, although if all the values occur only once then MODE returns an error value. This is the case for S = {5, 2, -1, 3, 7, 4, 0, 6}. Thus MODE(D3:D10) = #N/A.

Excel 2010/2013 provide an array function MODE.MULT, which is useful for multimodal data by returning a vertical list of modes. When we highlight C19:C20 and enter the array formula =MODE.MULT(C3: C10) and then press Ctrl-Alt-Enter, we see that both modes are displayed.

Excel 2010/2013 also provide the function MODE.SNGL which is equivalent to MODE.

Geometric Mean

Definition 4: The geometric mean of the data set S is calculated by

This statistic is commonly used to provide a measure of average rate of growth as described in Example 5.

Example 5: Suppose the sales of a certain product grow 5% in the first two years and 10% in the next two years, what is the average rate of growth over the 4 years?

If sales in year 1 are $1 then sales at the end of the 4 years are (1 + .05)(1 + .05)(1 + .1)(1 + .1) = 1.334. The annual growth rate r is that amount such that (1+r)⁴ = 1.334. Thus r = 1.334^1/4 – 1 = .0747.

The same annual growth rate of 7.47% can be obtained in Excel using the formula GEOMEAN(H7:H10) – 1 = .0747.

Harmonic Mean

Definition 5: The harmonic mean of the data set S is calculated by the formula

The harmonic mean can be used to calculate an average speed, as described in Example 6.

Example 6: If you go to your destination at 50 mph and return at 70 mph, what is your average rate of speed?

Assuming the distance to your destination is d, the time it takes to reach your destination is d/50 hours and the time it takes to return is d/70, for a total of d/50 + d/70 hours. Since the distance for the whole trip is 2d, your average speed for the whole trip is

This is equivalent to the harmonic mean of 50 and 70, and so can be calculated in Excel as HARMEAN(50,70), which is HARMEAN(G7:G8) from Figure 2.