Formulas

AOV formula: how to calculate average order value

Average order value (AOV) is the revenue you take per order.

About the Shopify connector

It moves with what people buy and how much of it, so it is the first place to look when revenue changes but order count does not. The mistakes are in which orders and which revenue go in, and in averaging figures that are already averages.

The formula

AOV = revenue ÷ number of orders

In Google Sheets, with orders in column B and revenue in column C:

=IF(B2=0, "", C2/B2)

The IF leaves a day with no orders blank instead of returning #DIV/0!. Format as currency to two decimal places.

Decide what revenue means before you build the column. Net sales (after discounts, before shipping and tax) is the usual choice, because it is what the order is actually worth to you. Whichever you pick, use the same one every time, and say which it is in the column heading.

A worked example

The total row divides total sales by total orders: =C6/B6, which is 8,000 ÷ 175 = 45.71.

The common mistake

=AVERAGE(D2:D5) returns 48.00. The AOV for the four days is 45.71.

Each day's AOV is already an average, and the days carry different numbers of orders. Averaging them gives Tuesday's 25 orders the same say as Wednesday's 60. Divide total revenue by total orders for the period and the weighting takes care of itself. The same applies when you combine products or months.

The other mistake is in the inputs. Refunded orders and test orders both count as orders in a raw export, and a refund can carry a negative value. Leave test orders out entirely. For refunds, either exclude the order or use a revenue figure that has the refund taken off, and be consistent about which, because the two give different AOVs on the same week.

What counts as good

An AOV is only good against your own history. Read it alongside conversion rate, because a change that lifts one and lowers the other may leave revenue where it was. Compare this month with the same month last year rather than with a figure from another business, whose products and prices have nothing to do with yours. The published figures make the point for you: Triple Whale reports a median AOV of $61.22 on paid traffic across the ecommerce brands on its platform (August 2025 to July 2026), while Shogun reports a median of $312 across 2,934 Shopify stores with 250 or more orders in the half year (H1 2026). Both are real medians from large panels, and they are $250 apart, because the panels are different shops.

Keeping the inputs current

A scheduled Fresh Sheets report can keep Shopify orders and sales by day in a tab of their own, for example one called Shopify. Its Subtotal column is the sum of line prices after discounts and before refunds and shipping. Where your prices exclude tax, that is the net sales figure above; where they include it, the subtotal includes it too.

Fresh Sheets replaces that tab's contents on every refresh, so keep your formulas on a separate tab. This version finds each column by its heading, so it still works if you add a field and the columns move:

=SUM(INDEX('Shopify'!A:Z, 0, MATCH("Subtotal", 'Shopify'!1:1, 0))) / SUM(INDEX('Shopify'!A:Z, 0, MATCH("Orders", 'Shopify'!1:1, 0)))

Change the quoted headings to match the ones in your tab.