How to calculate average order value in Excel

The formula, and why AVERAGE gives the wrong answer

AOV answers one question: when someone checks out, how much do they spend? So the bottom of the fraction is orders, not rows. Most shop exports (Shopify, Etsy, WooCommerce, Square) list one row per item, so order #1003 with a mug, a print and a tote bag takes three rows.

That's why =AVERAGE(E2:E14) is wrong here. It averages the 13 line items and gives $20.77, which is the average item price, not the average order. Real orders are bigger than that because people buy more than one thing.

UNIQUE(A2:A14) lists each order ID once, and COUNTA counts them: 8 orders. Divide total revenue by that and you get the real AOV.

Counting orders in older Excel (2019 and earlier)

UNIQUE needs Excel 365 or 2021. In older versions, SUMPRODUCT(1/COUNTIF(A2:A14,A2:A14)) counts distinct values: each row of order #1003 counts as 1/3, so the three rows add up to 1. It gives 8 here, the same as COUNTA(UNIQUE()).

It fails if the ID column has a blank cell (COUNTIF returns 0 and you get #DIV/0!), so trim the range to the rows that have data. If your export already has one row per order with an order total column, skip all of this: =AVERAGE(order_total_column) is the AOV.

$0 orders, refunds and test orders

Order #1007 is a free replacement mug. It's a real shipment but not a sale, and it pulls the AOV down. Decide once whether orders like that count, then write the rule next to the number. To leave out orders with no paid lines, count only IDs that have a line above $0: that gives 7 orders and an AOV of $38.57.

Refunds work best as their own negative lines with the same order ID, so revenue stays net and the order still counts once. If a whole order was refunded, most people take it out of both the revenue and the order count. Delete test orders before you start. One $1 test order can move a small shop's AOV by a few dollars.

AOV by month and by channel

One overall number hides the trend. Put the month start and end in cells (H2 and H3) and use the same two dates in both halves: SUMIFS adds October's revenue, and FILTER keeps October's order IDs before UNIQUE counts them. October comes to $212 over 7 orders.

For channels, swap the date test for the channel: =SUMIFS(E2:E14,C2:C14,"Market")/COUNTA(UNIQUE(FILTER(A2:A14,C2:C14="Market"))) gives $25.67 for the market stall and the Online version gives $38.60. That gap is useful to know. It might mean adding a bundle at the stall, or it might just be what people buy on the spot.

Copy the formula down a table of month starts (and EOMONTH for the ends) to get AOV per month next to the order count. Revenue grows from more orders, bigger orders or both, and that table shows which one moved.

Median order value: the check on one big order

An average can be dragged by one large order. The median is the middle order, so it shows what a typical customer spends. To get it from line items, first total each order: SUMIFS(E2:E14,A2:A14,UNIQUE(A2:A14)) returns one total per order (30, 25, 65, 12, 40, 40, 0, 58). MEDIAN then picks the middle.

Here the median is $35 and the mean is $33.75, so they tell the same story. When the mean sits well above the median, a few big orders are carrying it. Report both and look at those orders before you plan around the average.