Level 1 · A typical order line with AVERAGE, MEDIAN and MODE.SNGL

Three functions answer “what is typical?” and they often disagree. The disagreement is not a problem to hide: it tells you what the data looks like. This sheet holds every completed order line from Tuesday, September 16, 2025: 38 lines, from a $10.99 Dutch oven to a $3,198 pair of bed frames.

The mean: AVERAGE

=AVERAGE(F2:F39)

AVERAGE adds the numbers and divides by how many there are. Here 11,329.09 ÷ 38 gives 298.13. Every value has a say in the mean, in proportion to its size, which is both its strength and its weakness: the $3,198 line in F6 weighs as much as 32 ordinary $100 lines.

AVERAGE ignores empty cells and text in a range. It does count zeros, so a 0 typed for “no data” drags it down, while a blank cell does not.

The median: MEDIAN

=MEDIAN(F2:F39)

MEDIAN sorts the values and takes the one in the middle. With an odd count that is one value. With 38 values there are two middle ones, the 19th and the 20th, so Excel averages them: (60.99 + 71.96) ÷ 2 = 66.475, shown as $66.48.

Half of the lines are below the median and half above, whatever the biggest line is. Change the $3,198 in F6 to $30,000 and the median does not move at all, while the mean jumps by about $700.

The mode: MODE.SNGL

=MODE.SNGL(D2:D39)

The mode is the value that appears most often. It is most useful on small whole numbers like quantities, sizes or ratings, where values repeat. Here it returns 1: 19 of the 38 lines are a single unit, and 10 more are two units.

Function	What it returns	Here
AVERAGE	sum ÷ count	$298.13
MEDIAN	the middle value	$66.48
MODE.SNGL	the most frequent value	1 unit

MODE.SNGL returns #N/A when no value repeats, which is the usual result on prices with cents. When two values tie, it returns the one that appears first in the range. MODE is the older name and gives the same answer; Google Sheets has both.

Mean far above median: what to tell Megan

The mean is about 4.5 times the median. That gap is the signal: a few large lines pull the mean up while most lines are small. Only 8 of the 38 lines are above $298.13. Take out the three lines over $1,000 (F6, F27 and F29) and the mean falls to $151.44, while the median barely moves, to $54.99.

So the honest sentence is: “A typical order line on September 16 was about $66; three large furniture and decor lines lifted the average to $298.” Revenue, order values and salaries almost always look like this. Quote the median as typical, and keep the mean for totals, because mean × count gives back the day’s revenue and the median does not.

Common mistakes

• Quoting the mean as typical on skewed data like order values.
• Taking the mode of revenue. Amounts with cents rarely repeat, so you get #N/A or a meaningless coincidence. Use the mode on quantities and categories.
• Including the header or a total row in the range: a total row doubles the sum and wrecks the mean.
• Zeros for missing data. AVERAGE and MEDIAN count a 0 but skip a blank cell.