Excel Functions: AVERAGE, MEDIAN, MODE

When we created a Box Plot recently, one of the measures was the MEDIAN.

  • For an odd set of numbers, the MEDIAN is the number in the middle of the set.
  • For an even set of numbers, the MEDIAN is the average of the two numbers in the middle.

MEDIAN is one of the central tendency functions, along with AVERAGE and MODE.

AVERAGE Function

If, like me, you’re not a statistician, you probably use the AVERAGE function, more often than you use MEDIAN or MODE.

  • The AVERAGE is the SUM of the numbers, divided by the COUNT of the numbers

MODE Function

Do you ever use the MODE function? I used it in my statistics class at university, but not much since then, and we won’t talk about how long ago that was!

  • The MODE function returns the most frequently occurring number in the set
  • If there aren’t any duplicate numbers, the result is an #N/A error
  • If there is a tie, the most frequent number that occurs first is the result

Comparing AVERAGE, MEDIAN and MODE

Below, you can see a couple of very simple examples of measuring a small set of numbers.

In the first example, the numbers are symmetrically distributed, as you can see in the COUNT chart.

The second chart shows that the AVERAGE, MEDIAN and MODE are the same.

centraltendency01

In the next example, the numbers are NOT symmetrically distributed, as you can see in the COUNT chart.

The second chart shows that the AVERAGE, MEDIAN and MODE are different.

centraltendency02

Use the Interactive Workbook

If you would like to play with the sample workbook, you can change the numbers in the interactive Excel workbook, shown below. There are two worksheets with number sets – one is symmetrically distributed, and the other is not.

To get the score counts, I used the FREQUENCY function.

_____________________