Photo by Rubaitul Azad on Unsplash
Why a free-shipping threshold needs careful maths
Offering free shipping feels like a growth tactic, but it costs real money. If the basket value that triggers free delivery is set too low, every order erodes margin. Set it too high and customers abandon their carts. The goal is to find the minimum order value at which the gross profit from the sale at least covers the shipping cost, while still leaving a contribution toward overheads and profit.
This guide builds a seven-input Google Sheets calculator that you publish as a live web form using the GSheetPress Calculator tool. Anyone with the link can enter their own numbers and read results instantly, without editing your workbook.
Key terms explained
- Gross margin (%): The percentage of a sale price that remains after deducting the direct cost of the product — for example, a 40% margin means 40 cents of every dollar is profit before overheads.
- Contribution margin: The portion of gross profit that contributes to fixed costs and net profit after variable costs (like shipping) are paid.
- Free-shipping threshold: The minimum cart total a customer must reach before shipping is offered at no extra charge.
- Average order value (AOV): The mean basket size across all recent orders; used to judge whether the threshold is realistic for your customers.
- Shipping zone: A geographic band that determines the carrier rate; orders to distant zones cost more to fulfil.
- Blended shipping cost: A single average shipping cost calculated by weighting each zone's rate by the share of orders it typically receives.
- Discount rate: A percentage reduction applied to the selling price, e.g. a 10% site-wide promotion. This reduces effective margin and must be factored in.
- Markup vs margin: Markup is calculated on cost price; margin is calculated on selling price. This guide uses margin throughout.
- Break-even threshold: The basket value at which gross profit exactly equals shipping cost — no contribution to overheads, no loss.
- Contribution target: An additional profit floor you want each order to exceed beyond covering shipping.
What this model covers and what it excludes
The model estimates a minimum basket value based on gross margin, blended shipping cost, discount rate, and a contribution target. It compares the result with your actual AOV so you can judge feasibility at a glance.
Excluded: returns and refund costs, packaging materials, payment-processing fees, fulfilment labour, carrier surcharges (fuel, residential, oversize), and tax. Each of these lowers your effective margin further and should be investigated separately before you finalise a threshold for real use. All assumptions — especially margin and blended shipping cost — need verification against your own P&L and carrier invoices.
The core formula explained
The fundamental relationship is:
Break-even threshold = Blended shipping cost / Gross margin (as a decimal)For example, if shipping costs £4.50 and your gross margin is 40% (0.40):
Break-even threshold = 4.50 / 0.40 = £11.25At a basket of £11.25 the gross profit (£4.50) exactly cancels the shipping cost. To also meet a contribution target — say you want £2.00 of contribution per order above shipping — add it back:
Threshold with contribution = (Shipping cost + Contribution target) / Gross margin decimal= (4.50 + 2.00) / 0.40 = £16.25When a discount is active, the effective selling price is lower, so effective margin shrinks. We apply the discount to margin before dividing:
Effective margin = Gross margin % x (1 - Discount rate %)All of this is wired into the calculator below.
Prerequisites
- A Google account with access to Google Sheets.
- A GSheetPress account — sign up at gsheetpress.com/admin/signup.html. The Calculator feature requires a plan that includes Calculator entitlement; check the pricing page to confirm your tier includes it before starting.
- The GSheetPress add-on installed in Google Sheets via the Google Workspace Marketplace. After installation, reload your spreadsheet and confirm the Extensions → GS2Web SaaS menu is visible.
- A dedicated sample workbook created solely for this calculator. Do not use a workbook that holds real business or customer data, because the sharing step later makes the spreadsheet editable by anyone who has the link.
Step 1 — Set up your sample workbook
- Open Google Sheets and create a new blank workbook. Name it Free Shipping Threshold Calculator – Sample.
- Rename the default sheet tab CalcData. This is where your helper formulas will live.
- Keep a second blank tab if you want scratch space; the calculator add-on will create its own tab automatically.
Step 2 — Build the helper data on CalcData
Before adding result formulas, populate a small reference area that stores zone rates and blended shipping cost. This keeps formulas short and auditable.
Zone rate table (rows 2–5, columns J–L)
In columns J, K, and L of your CalcData tab, enter the following. Row 1 is a header row.
| Cell | Value to enter | Purpose |
|---|---|---|
| J1 | Zone | Header |
| K1 | Rate (£) | Header |
| L1 | Order share (%) | Header |
| J2 | Zone 1 (local) | Label |
| K2 | 3.00 | Carrier rate — replace with your actual rate |
| L2 | 50 | 50% of orders go to this zone |
| J3 | Zone 2 (regional) | Label |
| K3 | 4.50 | Replace with actual rate |
| L3 | 30 | 30% of orders |
| J4 | Zone 3 (national) | Label |
| K4 | 6.50 | Replace with actual rate |
| L4 | 15 | 15% of orders |
| J5 | Zone 4 (remote) | Label |
| K5 | 9.00 | Replace with actual rate |
| L5 | 5 | 5% of orders |
The shares in column L must sum to 100. Verify with =SUM(L2:L5) entered in cell L6; it must show 100 before you proceed.
Blended shipping cost (cell N2)
Click cell N1 and type Blended shipping cost as a header. Click N2, type =, paste the rest of the formula, and press Enter:
=SUMPRODUCT(K2:K5, L2:L5/100)This multiplies each zone rate by its share fraction and sums the products to produce a weighted average. With the sample data the result is £4.35. This value is what you enter as the default in the calculator's Blended Shipping Cost input field.
Google publishes guidance on how SUMPRODUCT works; see the official SUMPRODUCT documentation if you want to understand the zone-weighting formula in more detail.
Step 3 — Create the calculator in GSheetPress
Open your sample workbook. Click Extensions → GS2Web SaaS → Calculator → New Calculator. A dialog will appear.
- Enter a calculator name, for example: Free Shipping Threshold.
- Set the number of inputs to 7. This is the count of user-facing input fields, not including the Calculate button.
- When asked whether to include graphs, choose No for this model.
- Click Create (or the equivalent confirm button in the dialog).
GSheetPress inserts a new tab — typically named after your calculator — and pre-fills row 1 with the fixed headers across columns A to H: Labels, Input Type, Required, Data, Default, ToolTips, Inputs, Rules. Do not rename, move, or delete these headers.
Because you specified 7 inputs, the add-on also inserts the Calculate button in row 9 (input rows 2–8, then button at row N+2 = 9), a merged Results separator in row 10, result column headings in row 11, and result values starting from row 12.
Understanding columns E and G before you begin
Column E (Default) stores the value pre-filled in each form field when the web calculator loads. Column G (Inputs) is overwritten by whatever the user submits at runtime. All result formulas must reference column G (G2 through G8) — never column E. To test your formulas before publishing, copy E2:E8 and use Paste special → Values only into G2:G8 so your formulas have numbers to work with. Never store result formulas in column G because web submissions will overwrite those cells.
Row 2 — Gross margin (%)
- A2 — Label: Gross Margin (%)
- B2 — Input Type: Number
- C2 — Required: Tick the checkbox (mark as required)
- D2 — Data: Leave blank
- E2 — Default: 40
- F2 — ToolTips: Enter your gross margin as a percentage, e.g. 40 for 40%. Calculated on selling price, not cost.
- G2 — Inputs: Leave blank (managed by the add-on at runtime)
- H2 — Rules: Leave blank
Row 3 — Discount rate (%)
- A3 — Label: Discount Rate (%)
- B3 — Input Type: Number
- C3 — Required: Leave unticked (zero is a valid entry)
- D3 — Data: Leave blank
- E3 — Default: 0
- F3 — ToolTips: Enter any site-wide discount percentage currently active, e.g. 10 for a 10% sale. Enter 0 if no discount applies.
- G3 — Inputs: Leave blank
- H3 — Rules: Leave blank
Row 4 — Blended shipping cost (£)
- A4 — Label: Blended Shipping Cost (£)
- B4 — Input Type: Number
- C4 — Required: Tick the checkbox
- D4 — Data: Leave blank
- E4 — Default: 4.35
- F4 — ToolTips: Enter your weighted average shipping cost across all zones. See the CalcData tab for how to calculate this from your carrier rates.
- G4 — Inputs: Leave blank
- H4 — Rules: Leave blank
Row 5 — Contribution target (£)
- A5 — Label: Contribution Target (£)
- B5 — Input Type: Number
- C5 — Required: Leave unticked
- D5 — Data: Leave blank
- E5 — Default: 2
- F5 — ToolTips: The minimum gross profit in £ that you want each order to generate after shipping is covered. Enter 0 to calculate the break-even threshold only.
- G5 — Inputs: Leave blank
- H5 — Rules: Leave blank
Row 6 — Average order value (£)
- A6 — Label: Average Order Value (£)
- B6 — Input Type: Number
- C6 — Required: Tick the checkbox
- D6 — Data: Leave blank
- E6 — Default: 35
- F6 — ToolTips: Your current mean basket value across recent orders. The result section will compare the threshold against this figure.
- G6 — Inputs: Leave blank
- H6 — Rules: Leave blank
Row 7 — Currency symbol
- A7 — Label: Currency Symbol
- B7 — Input Type: Text
- C7 — Required: Leave unticked
- D7 — Data: Leave blank
- E7 — Default: £
- F7 — ToolTips: Enter your currency symbol, e.g. £, $ or €. Used for display only; does not affect calculations.
- G7 — Inputs: Leave blank
- H7 — Rules: Leave blank
Row 8 — Monthly order volume
- A8 — Label: Monthly Order Volume
- B8 — Input Type: Number
- C8 — Required: Leave unticked
- D8 — Data: Leave blank
- E8 — Default: 200
- F8 — ToolTips: Approximate number of orders per month. Used to estimate total monthly shipping cost absorbed if free shipping is offered.
- G8 — Inputs: Leave blank
- H8 — Rules: Leave blank
Row 9 — The Calculate button (generated by the add-on)
Row 9 is the Calculate button row. The add-on pre-fills it. Verify but do not alter these cells:
- A9: Calculate
- B9: Button
- D9: main
- E9: Reset
All other cells in row 9 should remain exactly as the add-on set them.
Rows 10 and 11 — Separator and result headings (generated)
Row 10 is a merged Result Fields separator inserted by the add-on. Row 11 holds the column headings for your result section. Leave both rows exactly as generated.
Step 4 — Enter result labels and formulas
Result labels go in column A starting at row 12. Result formulas go in column B of the same rows. Column B values are what the web calculator displays to users after they click Calculate.
To enter each formula: click the target cell, type =, then paste the rest of the formula text, and press Enter. Do not paste the leading equals sign as part of a copy — type it yourself to ensure Google Sheets recognises it as a formula.
Result row 12 — Effective margin
Click A12 and type: Effective Margin (%)
Click B12, type =, paste the remaining text, and press Enter:
=IF(G2="", "Enter gross margin", ROUND(G2 * (1 - G3/100), 2))This reduces the gross margin by the discount rate. With defaults (40% margin, 0% discount) the result is 40.00.
Result row 13 — Break-even threshold
Click A13 and type: Break-Even Threshold
Click B13, type =, paste, and press Enter:
=IF(B12="Enter gross margin", "Check inputs", IF(B12<=0, "Margin too low — threshold undefined", ROUND(G4 / (B12/100), 2)))This divides the blended shipping cost (G4) by the effective margin expressed as a decimal. With defaults: 4.35 / 0.40 = 10.88.
Result row 14 — Threshold with contribution target
Click A14 and type: Threshold with Contribution Target
Click B14, type =, paste, and press Enter:
=IF(B12<=0, "Margin too low", ROUND((G4 + G5) / (B12/100), 2))With defaults: (4.35 + 2.00) / 0.40 = 15.88.
Result row 15 — AOV gap
Click A15 and type: AOV vs Threshold Gap (£)
Click B15, type =, paste, and press Enter:
=IF(ISNUMBER(B14), ROUND(G6 - B14, 2), "N/A")A positive number means your average order already exceeds the recommended threshold. A negative number means customers typically spend less than needed. With defaults: 35.00 - 15.88 = +19.12.
Result row 16 — AOV as % of threshold
Click A16 and type: AOV as % of Threshold
Click B16, type =, paste, and press Enter:
=IF(ISNUMBER(B14), IF(B14=0, "N/A", ROUND(G6/B14*100, 1)), "N/A")Values above 100 mean the threshold is below your current AOV. With defaults: 35 / 15.88 x 100 = 220.4.
Result row 17 — Monthly shipping cost absorbed
Click A17 and type: Est. Monthly Shipping Cost Absorbed (£)
Click B17, type =, paste, and press Enter:
=IF(ISNUMBER(G4), ROUND(G4 * G8, 2), "Enter shipping cost and volume")This estimates total shipping outlay if every order qualifies for free shipping. With defaults: 4.35 x 200 = 870.00. This is a planning figure; actual eligible orders depend on the distribution of basket values relative to the threshold.
Result row 18 — Recommendation
Click A18 and type: Recommendation
Click B18, type =, paste, and press Enter:
=IF(NOT(ISNUMBER(B14)), "Fix inputs above", IF(B15>=0, "Threshold appears achievable — AOV already exceeds it", "Threshold exceeds AOV — review margin, shipping cost, or contribution target"))
Step 5 — Verify the worked example
Copy E2:E8, then right-click G2 and choose Paste special → Values only. Your results section should immediately show the following:
| Result label | Cell | Expected value |
|---|---|---|
| Effective Margin (%) | B12 | 40.00 |
| Break-Even Threshold | B13 | 10.88 |
| Threshold with Contribution Target | B14 | 15.88 |
| AOV vs Threshold Gap (£) | B15 | 19.12 |
| AOV as % of Threshold | B16 | 220.4 |
| Est. Monthly Shipping Cost Absorbed (£) | B17 | 870.00 |
| Recommendation | B18 | Threshold appears achievable — AOV already exceeds it |
Alternative scenario — tight margin with active discount
Paste these values into G2:G8 manually to verify edge-case behaviour: Gross Margin = 25, Discount Rate = 10, Blended Shipping Cost = 6.00, Contribution Target = 3.00, AOV = 28, Currency Symbol = £, Monthly Order Volume = 150.
| Result label | Cell | Expected value |
|---|---|---|
| Effective Margin (%) | B12 | 22.50 |
| Break-Even Threshold | B13 | 26.67 |
| Threshold with Contribution Target | B14 | 40.00 |
| AOV vs Threshold Gap (£) | B15 | -12.00 |
| AOV as % of Threshold | B16 | 70.0 |
| Est. Monthly Shipping Cost Absorbed (£) | B17 | 900.00 |
| Recommendation | B18 | Threshold exceeds AOV — review margin, shipping cost, or contribution target |
The negative gap and sub-100% AOV ratio signal that at current basket sizes, free shipping at the required threshold is not commercially viable without changes to pricing, carrier negotiation, or contribution expectations. Use the GSheetPress support page if you encounter formula errors during testing.
Troubleshooting common formula errors
B13 or B14 shows "Margin too low — threshold undefined"
This appears when B12 is zero or negative. It means either the gross margin input (G2) is 0 or the discount rate (G3) equals or exceeds the gross margin. A gross margin of 0% means no revenue remains after product cost, so a threshold cannot be mathematically defined. Check G2 and G3 values.
B15 or B16 shows "N/A"
These formulas check whether B14 is a number using ISNUMBER. If B14 contains a text error message (from B12 being invalid), B15 and B16 return N/A. Fix the upstream inputs first.
B17 shows "Enter shipping cost and volume"
G4 is empty or contains text. Make sure the Blended Shipping Cost input (row 4) has a valid number in G4. If testing before publishing, ensure you pasted E4 into G4 as a value.
Zone shares in L6 do not sum to 100
Add or remove zone rows in J:L and update the SUMPRODUCT range in N2 to match. For example, if you have five zones extending to row 6, change the formula to =SUMPRODUCT(K2:K6, L2:L6/100). The blended cost updates automatically.
The add-on tab does not appear after clicking New Calculator
Reload the spreadsheet and try again. If the Extensions menu is missing, reinstall the add-on from the Google Workspace Marketplace and confirm your GSheetPress account plan includes Calculator entitlement. See the pricing page for plan details.
Frequently asked questions
What if my gross margin varies by product category?
The model uses a single blended margin. If you sell across very different categories — say, low-margin electronics and high-margin accessories — calculate a weighted average margin by multiplying each category's margin by its share of revenue, then sum the products. Enter that blended figure into the Gross Margin field. For category-specific analysis, duplicate the calculator tab and adjust inputs for each segment.
Can I add more shipping zones or change zone rates without rebuilding the calculator?
Yes. The zone rates live in the CalcData tab's helper table (J2:L5), which feeds the N2 blended-cost formula. You can add rows (for example J6, K6, L6 for a fifth zone) and extend the SUMPRODUCT range in N2 to K2:K6 and L2:L6. The blended cost in N2 updates automatically. Users of the published calculator enter the blended cost as a single input, so they always receive current rates as the default without any structural change to the calculator form itself.
Why should I use a dedicated sample workbook rather than my main business spreadsheet?
Publishing the calculator requires setting the Google Sheet to allow anyone with the link to edit it. If that spreadsheet also contains order history, customer data, pricing lists, or financials, all of that data becomes editable by anyone who finds or shares the link. A dedicated sample workbook with only the calculator data eliminates that risk entirely. You can review GSheetPress table and data-publishing capabilities on the table details page.
Publish and test your web calculator
Before publishing, make sure all seven input rows are configured, all result formulas in B12:B18 return the expected values when G2:G8 contain the default values, and the Calculate button in row 9 is intact.
Step 1 — Share the spreadsheet correctly
- In your sample workbook, click Share (top-right corner).
- Under General access, change the setting to Anyone with the link.
- Set the role to Editor.
- Click Done.
Important: Setting the role to Editor means anyone who obtains the spreadsheet link can open and modify it. This is why you must use only the dedicated sample workbook and must never paste sensitive business data into it.
Step 2 — Publish the calculator
- Click on the calculator tab (the one the add-on created, not CalcData).
- Click Extensions → GS2Web SaaS → Calculator → Publish Calculator.
- Wait for the confirmation message. The add-on will indicate that the calculator has been published successfully.
- In the same menu or dialog, click Get Links (or the equivalent refresh option) to load the latest published calculator registry.
Step 3 — Find and open your public link
- The add-on opens or updates a sheet called Calculator Link (or similar — the name matches what GSheetPress generates).
- Find the row that matches your calculator's Sheet Name.
- Column C contains the public Link — this is the URL you share with users. Click it to open the live calculator in a browser.
- Column B contains the Edit Link — this opens the add-on editor, not the public form. Do not share column B links externally.
- If the row is not visible, click Get Links again to refresh the sheet.
Step 4 — Test with the worked inputs
- Open the public link (column C) in a browser.
- Enter the default test values: Gross Margin = 40, Discount Rate = 0, Blended Shipping Cost = 4.35, Contribution Target = 2, AOV = 35, Currency Symbol = £, Monthly Order Volume = 200.
- Click Calculate.
- Confirm the results match the expected values from the worked example table: Break-Even Threshold = 10.88, Threshold with Contribution Target = 15.88, AOV Gap = 19.12, AOV % of Threshold = 220.4, Monthly Shipping Absorbed = 870.00, Recommendation = Threshold appears achievable.
- Also test the alternative scenario inputs (Gross Margin = 25, Discount Rate = 10, Blended Shipping Cost = 6.00, Contribution Target = 3, AOV = 28, Monthly Order Volume = 150) and confirm the threshold comes to 40.00 and the recommendation warns that the threshold exceeds AOV.
Step 5 — Embed on your website (optional)
If you want to embed the calculator directly in a webpage, return to the Calculator Link sheet and copy the value in column D (Iframe). Paste this code into an HTML block on your website. The embedded calculator remains linked to the same published instance; republishing the same existing calculator retains its link and updates the embedded version automatically.
For more on embedding and publishing options, visit the GSheetPress shop details page.