Excel statistics practice: MEDIAN, RANK, LARGE and STDEV

The statistics functions, with examples

Every example runs on store-performance.xlsx, the same sheet the exercises use, and the results are calculated from that data.

• MEDIAN

  The middle value once the numbers are sorted. One unusually high or low value barely moves it, unlike the average.

  MEDIAN(number1, [number2], …)

  Example

  =MEDIAN(C2:C11)→returns$39,975.00

• LARGE

  Returns the k-th largest value: LARGE(range, 2) is the second highest. SMALL works from the bottom.

  LARGE(array, k)

  Example

  =LARGE(C2:C11, 2)→returns$52,900.00

• RANK.EQ

  A value's position in a list, with 1 for the largest by default. Equal values share a rank.

  RANK.EQ(number, ref, [order])

  Example

  =RANK.EQ(C2, $C$2:$C$11)→returns3

• MODE.SNGL

  The value that appears most often. Returns #N/A if no value repeats.

  MODE.SNGL(number1, [number2], …)

  Example

  =MODE.SNGL(E2:E11)→returns7

• STDEV.S

  How spread out the values are around the average, for a sample. A bigger number means more variation.

  STDEV.S(number1, [number2], …)

  Example

  =STDEV.S(C2:C11)→returns$10,628.37

Common mistakes

1. 1

   Relying on the average when the data is lopsided

   A single very large value, like a flagship store, pulls the average up so it no longer describes a typical store. The median doesn't move much. When values are uneven, report the median, or both.

2. 2

   Not locking the list in RANK.EQ

   Copy =RANK.EQ(C2,C2:C11) down and the list moves with each row, so the ranks come out wrong. Use $C$2:$C$11 so every row is ranked against the same list.

3. 3

   Picking STDEV.S when you have every value

   STDEV.S is for a sample of a larger group. If the data is the whole group, like every store you run, STDEV.P is the matching choice. The difference is biggest for short lists.