How to find the selling price that achieves your target gross margin with excel goal seek
- Step 1Build the margin formula on a price cell — Have a price cell (the input) and a gross-margin cell that computes
(Price − COGS) / Price, using supported arithmetic and single-cell references. If COGS is a rollup, precompute it into one cell — don't useSUM(range)or a lookup inside the margin formula. - Step 2Upload the .xlsx — Drop the workbook onto the tool —
.xlsxonly, parsed by SheetJS in your browser so cost and pricing data stay local. Goal Seek requires the Developer tier. - Step 3Set the margin cell as the target — Enter the margin cell in
targetCell(e.g.D4). It must hold the margin formula, not a typed percentage. - Step 4Enter the target margin — Set
targetValueto your goal. Use the form your formula produces:0.40if the margin cell returns a fraction, or40if it's already multiplied by 100. Match the units or the solver hits the wrong price. - Step 5Set the price cell as the input — Enter the price cell in
inputCell(e.g.B2). Seed it with a plausible price so the search brackets around a realistic range. SetsheetNameif the pricing model isn't on the first sheet. - Step 6Run and apply the price — Read the target price from the report (six decimals). Round to your pricing convention (e.g.
.99endings) and re-check the margin after rounding — rounding the price shifts the margin slightly. The tool returns a report; enter the price into your model.
Target-margin Goal Seek setup
Options for solving the price that hits a target margin. Watch the fraction-vs-percent units on the target value.
| Option | Value | Notes |
|---|---|---|
targetCell | margin cell, e.g. D4 | Must hold (Price-COGS)/Price (or your variant). |
targetValue | 0.40 or 40 | Match the formula's units: fraction vs ×100 percent. |
inputCell | price cell, e.g. B2 | Seed with a realistic price for a sensible bracket. |
sheetName | blank or pricing tab | Defaults to first sheet. |
Markup vs margin — they're not the same target
A frequent pricing error. COGS = 60. Decide which ratio your formula computes before setting the target.
| Concept | Formula | Target 40% gives price |
|---|---|---|
| Gross margin | (Price - COGS) / Price | 100.00 (margin on price) |
| Markup on cost | (Price - COGS) / COGS | 84.00 (markup over cost) |
| Margin as ×100 percent | ((Price-COGS)/Price)*100 | set targetValue = 40, not 0.40 |
| Margin with channel fee f | (Price - COGS - Price*f) / Price | higher price to absorb the fee |
Cost-structure shapes and whether they solve here
What the evaluator can and can't handle inside the margin formula.
| COGS build | Example | Solves? |
|---|---|---|
| Single cost cell | (B2 - C2) / B2 | Yes |
| Inline arithmetic | (B2 - (C2+C3+C4)) / B2 | Yes — explicit cells |
| Range rollup | (B2 - SUM(C2:C9)) / B2 | No — range returns NaN |
| Lookup cost table | (B2 - VLOOKUP(...)) / B2 | No — VLOOKUP returns NaN |
| Fee as a fraction of price | (B2 - C2 - B2*0.03) / B2 | Yes — arithmetic only |
Cookbook
Target-margin price solves across common pricing structures. Each shows the cells, the options, and the report.
Basic 40% gross margin
COGS is 60. Find the price for a 40% gross margin where margin = (Price − COGS)/Price.
Sheet1: B2 = 100 (input — price, seed) C2 = 60 (COGS) D4 = (B2 - C2) / B2 Options: targetCell = D4 targetValue = 0.40 inputCell = B2 Report: ✓ converged after 39 iteration(s). Input cell: B2 = 100.000000 Output cell: D4 = 0.400000 → Sell at $100 for a 40% margin.
Margin expressed as a percentage (×100)
If the margin cell multiplies by 100, set targetValue to 40, not 0.40. Unit mismatch is the top pricing error.
Sheet1: B2 = 100, C2 = 60 D4 = ((B2 - C2) / B2) * 100 Options: targetCell = D4 targetValue = 40 (not 0.40!) inputCell = B2 Report: ✓ converged after 39 iteration(s). Input cell: B2 = 100.000000 Output cell: D4 = 40.000000
Price that absorbs a 3% payment-processing fee
The processor takes 3% of price. To keep a true 35% margin, the formula nets the fee out before dividing.
Sheet1: B2 = 100 (input — price) C2 = 55 (COGS) D4 = (B2 - C2 - B2*0.03) / B2 Options: targetCell = D4 targetValue = 0.35 inputCell = B2 Report: ✓ converged after 40 iteration(s). Input cell: B2 = 88.709677 Output cell: D4 = 0.350000 → ~$88.71 list price holds 35% after the 3% fee.
Blended COGS rolled into one cell
Material + labour + freight, summed into a single cost cell so the margin formula stays range-free and supported.
Sheet1: C2 = 30 (material), C3 = 18 (labour), C4 = 7 (freight) C9 = C2 + C3 + C4 (blended COGS = 55) B2 = 100 (input — price) D4 = (B2 - C9) / B2 Options: targetCell = D4 · targetValue = 0.45 · inputCell = B2 Report: ✓ converged after 40 iteration(s). Input cell: B2 = 100.000000 Output cell: D4 = 0.450000 → C9 must be re-saved so its cache is current.
Unreachable margin returns a best approximation
Gross margin can never reach 100% while COGS is positive (you'd need infinite price). The solver reports how close it got.
Sheet1: B2 = 100, C2 = 60 D4 = (B2 - C2) / B2 Options: targetCell = D4 · targetValue = 1.00 · inputCell = B2 Report: ⚠ did not fully converge (best residual: ~6e-3). Best found: Input cell: B2 = 60900.000000 (price runs away) Output cell: D4 = 0.999015 → 100% margin is impossible with positive COGS.
Edge cases and what actually happens
Target value units don't match the formula
Wrong priceIf the margin cell returns a fraction (0.40) but you set targetValue = 40, the solver chases a 4,000% margin and returns a runaway price. Decide whether your formula produces a fraction or a ×100 percentage and set the target to match — this is the most common pricing mistake.
Margin formula uses a range or VLOOKUP
NaN / search failsCost rolled up with SUM(C2:C9) (range syntax) or pulled from a VLOOKUP cost table returns NaN and the search fails. Precompute the cost into a single cell and reference that cell inside the margin formula instead.
Asking for margin at or above 100%
No convergenceGross margin (Price−COGS)/Price approaches but never reaches 1.0 (100%) while COGS is positive — it would need infinite price. The solver returns a best approximation with the price running large and a small residual. Target a reachable margin.
Markup confused with margin
Check the formulaA 40% markup on cost is not a 40% margin on price. If your formula divides by COGS instead of Price, you're solving markup. The solve still works, but the price means something different — confirm the denominator before trusting the number.
Blended-cost cell is stale
Cached values usedIf the margin formula references a blended-COGS cell that is itself a formula, the solver reads that cell at its cached value and won't recompute it — only the margin formula recalculates. A stale blended-cost cache yields the wrong price. Re-save the workbook to refresh caches before solving.
Price seeded at zero
Division by zeroWith price in the denominator, a zero-seeded price makes (Price−COGS)/Price divide by zero (NaN) at that point. Seed the input cell with a realistic positive price so the search avoids the singularity.
Rounded price shifts the margin
ExpectedThe solver returns an exact price like 88.709677. Rounding to a .99 retail ending changes the achieved margin slightly. After rounding, re-check the margin cell (or re-run the solve constrained to your price step) so you know the real margin you'll ship at.
Result not written back to the file
By designThe output is a text report with the target price, not a re-saved workbook. Read the price, round it to your convention, and enter it into your pricing sheet. The original file is never modified.
Frequently asked questions
How do I find the price for a target margin?
Set targetCell to your margin cell, targetValue to your goal (0.40 for 40% if the formula returns a fraction), and inputCell to your price cell. The solver varies the price until the margin equals the target and reports the price to six decimals.
Why is solving for margin harder than it looks?
Gross margin is (Price−COGS)/Price, so the unknown price sits in both the numerator and the denominator. There's no clean one-step rearrangement the way there is for markup. Goal Seek searches numerically and finds the price directly, which is why it's the right tool for margin targets.
Should I enter 0.40 or 40 as the target?
Match your formula. If the margin cell returns a fraction (e.g. =(B2-C2)/B2), enter 0.40. If it multiplies by 100 (=...*100), enter 40. Getting this wrong sends the solver after a wildly wrong margin and returns a runaway price.
Can I include channel or processing fees?
Yes, as long as they're arithmetic. A 3% processor fee is (B2 - C2 - B2*0.03)/B2. The fee is netted out before dividing, so the solved price holds the true margin after the fee. Keep everything to arithmetic and single-cell references.
What about a VLOOKUP cost table?
VLOOKUP isn't supported — it returns NaN and breaks the search. Look up the cost into a single cell first (or precompute it), then reference that one cell inside the margin formula. The same applies to SUM(range) cost rollups: collapse them into one cell.
Is markup the same as margin here?
No. Markup divides profit by cost; margin divides profit by price. A 40% markup on a $60 cost gives an $84 price; a 40% margin gives a $100 price. Make sure your formula's denominator matches what you mean before you trust the result.
Why can't I hit a 100% margin?
Gross margin on price can only reach 100% if COGS is zero — otherwise it approaches 1.0 as price grows but never gets there. The solver will report a best approximation with the price running away. Pick a margin under 100%.
Do I need Excel installed?
No. The workbook is parsed by SheetJS in your browser and solved locally — no Excel, no add-in, no upload. Cost structures and margin targets stay on your machine.
Why does my margin change after I round the price?
The solver gives an exact price (e.g. 88.71). Rounding to a retail ending shifts the margin a little because margin is sensitive to price. Re-check the margin cell after rounding so you know the actual figure you'll sell at.
Does it update my pricing sheet automatically?
No — it returns a text report. Read the price, round it, and enter it into your model. To freeze recalculated cells into static values afterward, use formula-to-value.
What tier do I need?
Developer tier. Developer accounts get up to 500 MB files and unlimited rows. Free, Pro, and Pro-media tiers can't run Goal Seek.
How do I verify my margin formula is supported?
Run the formula explainer on the margin cell to confirm it uses only supported functions and single-cell references. If it flags a range or VLOOKUP, collapse the cost into a single cell before solving.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.