Level 5 · Quiz: which formula is average order value?

AVERAGE is the first function everyone learns, which is exactly why it ends up in the wrong place. Average order value sounds like “the average of something”, but which something you average decides the answer. On Harbor & Pine’s November 2025, the candidates range from 182.73 to 461.77.

What one row means

Before averaging a column, ask what one row of it is.

range	one row is	AVERAGE gives	value
line totals	an order line	average line value	182.73
daily net_revenue	a day	average revenue per day	9,745.48
daily AOV	a day	average of the daily AOVs	461.77
revenue ÷ orders	the month	average order value	449.10

Only the last one divides by orders. November had 1,600 lines on 651 orders, 2.46 lines per order, so the average line is about 2.5 times smaller than the average order. Both numbers look like plausible prices, which is why the mistake survives a quick read.

Why the average of daily AOVs drifts

Each day’s AOV is right for that day. The problem is averaging them: AVERAGE gives every day the same weight. November 11 had 15 orders and Black Friday had 51, yet both count once. The month’s real AOV weights each day by its orders, which is the same as dividing the totals:

=SUM(D2:D31)/SUM(B2:B31)

The busy days around Black Friday had lower AOVs (discount codes, smaller gift baskets), so giving them the same weight as quiet days pushes the plain average up, to 461.77.

Matching the SQL answer

In the SQL holiday case, the November 2025 row says 651 orders, 292,364.40 of net revenue and an average order value of 449.10. That SQL computes SUM(revenue) / COUNT(DISTINCT order_id): a sum over a count. The spreadsheet formula that matches it is a sum over a sum of daily orders, and it matches to the cent. Whenever a spreadsheet KPI and a SQL KPI disagree, check first whether one of them is an average of averages.

Common mistakes

• Averaging line totals and calling it AOV: 182.73 instead of 449.10.
• Averaging per-day ratios in a total row. The same trap hits return rates, conversion rates and margins: a total row divides totals.
• Counting lines as orders. COUNT over the line table returns 1,600, and revenue ÷ 1,600 is the average line again. Orders need a distinct count, which is what the order block in level 2 gives you.