Calculate Average Order Value (AOV)

Excel & Google Sheets

How it works

Average order value is total revenue divided by the number of orders: $12,500 across 250 orders means the typical order is worth $50. It's the honest way to average — averaging per-order values by channel and then averaging the channels would let a 3-order channel count as much as a 300-order one. The one trap is a period with zero orders: dividing by zero returns #DIV/0! and pollutes every chart and average built on the column. Guard it with =IF(C2=0,0,B2/C2), which shows 0 for dead months instead of an error.

B2

Total revenue for the period or channel.

/C2

Divided by the number of orders in the same period.

IF(C2=0,0,…)

The guard: when there are no orders, show 0 instead of #DIV/0!.

When to use it

Use it to track AOV by month, compare channels or customer segments, measure whether a free-shipping threshold or bundle promo actually lifted basket size.

Common mistakes

• ✕#DIV/0! in periods with no orders.

  ✓A new channel or a dead month has 0 orders, and the plain division errors out. Use =IF(C2=0,0,B2/C2) — or "" if you prefer a blank cell.

• ✕Averaging the per-row AOVs to get an overall AOV.

  ✓AVERAGE of the AOV column ignores volume. Compute the overall figure from the totals: =SUM(B2:B13)/SUM(C2:C13).

• ✕Mixing gross and net revenue between rows.

  ✓If some rows include refunds and shipping and others don't, AOVs aren't comparable. Pick one definition and apply it to every row.