Set up price, rate, final price and saving columns
In Excel or Google Sheets, a percentage discount can be calculated with =A2*(1-B2) when A2 contains the original price and B2 contains a numeric percentage such as 15%. Place that formula in C2 for the sale price, and use =A2-C2 in D2 for the saving. Label each column so a percent cell cannot be confused with a money amount. Keep inputs and calculated outputs visually distinct in a shared worksheet.
This guide uses English-style formula names and comma-separated function arguments. Your spreadsheet locale can use different separators or decimal punctuation. The examples are hypothetical before-tax item prices in one currency, not checkout or accounting rules. Formulas can help audit a promotion's arithmetic, but the sheet still needs the actual eligibility terms. A correct calculation on a price that is not eligible remains an incorrect purchase comparison.
Store a percentage as a fraction of one
Type 15% explicitly into B2 for fifteen percent. Its numerical value is 0.15, which makes 1-B2 equal 0.85. If you enter the plain number 15 into an ordinary numeric cell, =A2*(1-B2) subtracts fifteen whole units of the multiplier and produces a negative result. Applying percentage formatting afterward does not automatically turn that stored 15 into 0.15; formatting and numerical value are separate parts of the worksheet.
You can choose a different convention with a whole-number percent input, such as 15, but then the formula must use =A2*(1-B2/100). Do not mix the conventions within a column. The NumUtility percentage field expects 15, whereas this guide's percentage-formatted spreadsheet cell contains 15%. Write that distinction in the column heading or an input note so copying values between the website and the workbook does not introduce a hundredfold error.
Verify one complete worksheet row
Enter 84.50 in A2 and 15% in B2. Formula =A2*(1-B2) gives an unrounded final price of 71.825, while =A2-C2 gives an unrounded saving of 12.675. At two-decimal currency display these appear as approximately 71.83 and 12.68, but formatting alone leaves the underlying values available to later formulas. That distinction matters when you sum several lines or compare a sheet with a receipt.
For a deliberate two-decimal final-price policy, use =ROUND(A2*(1-B2),2) in C2. If the row must reconcile exactly to its stated original price, derive the saved amount as =A2-C2 rather than independently rounding the unrounded saving. Here final 71.83 plus saving 12.67 returns 84.50. Rounding both intermediate quantities independently can make the displayed components disagree with the original by a cent. Choose and document the intended policy.
Copy formulas without moving a shared rate
If every row uses its own B-column discount, relative references such as =A2*(1-B2) are appropriate: copying down changes both row numbers. If one shared rate is stored in H1, use =A2*(1-$H$1). The dollar signs anchor that cell when the formula is copied. They do not denote currency in this expression. Keep the shared rate labelled and restrict editing to the intended input cell where practical.
Test the copied formula with a second deliberately different price. For example, 120 at 15% off should give 102 before rounding. If the second row reads a blank H2 instead of H1, it may show no reduction and still look plausible. Review the references in several rows rather than assuming a successful first-row result proves the entire column. A range containing formulas and manually overwritten numbers deserves particular attention.
Keep successive percentages separate from fixed coupons
For two eligible percentages in B2 and E2, use =A2*(1-B2)*(1-E2). An original 160 with 20% followed by 10% gives 115.20, saving 44.80 or 28% of the original. Use =1-C2/A2 for that effective discount only when A2 is positive, and format the ratio as a percentage. Adding 20% and 10% would incorrectly produce 30% off and a different final price.
A fixed coupon is a money amount and uses subtraction, not a percentage factor. With an explicitly eligible 10-unit coupon applied after 20% off 160, the calculation is 160 × 0.8 − 10 = 118. Applying the coupon before the percentage gives 120 instead. The offer terms select the order. Do not add a MAX-with-zero wrapper merely to hide an overlarge coupon; flag an invalid assumption unless the actual policy explicitly defines a zero-price floor.
| Task | Formula | Checked example |
|---|---|---|
| Sale price | =A2*(1-B2) | 84.50 and 15% → 71.825 |
| Rounded final price | =ROUND(A2*(1-B2),2) | 71.83; reconciled saving 12.67 |
| Two percentages | =A2*(1-B2)*(1-E2) | 160, 20%, 10% → 115.20 |
The website's percent input takes 15; these spreadsheet percentage cells contain 15%, numerically 0.15. Rounding policy is explicit.
Validate missing values, impossible ranges and reverse calculations
Require original prices to be numerical and nonnegative and percentage rates to lie between 0% and 100%. A missing rate is not automatically a verified zero discount. One explicit check is =IF(AND(ISNUMBER(A2),ISNUMBER(B2),A2>=0,B2>=0,B2<=1),A2*(1-B2),NA()). This returns an error marker for invalid input rather than a misleading numeric sale price. Use data validation alongside formula checks when a shared workbook needs clearer entry prompts.
To recover an original from a final price in C2 and a known rate in B2, use =C2/(1-B2) only when B2 is below 100% and the inputs are valid. A 100% reduction has no unique inverse. To find a discount rate from two prices, use =(A2-C2)/A2 only for positive A2 and a final amount between zero and A2. A zero original cannot supply a defined percentage denominator.
Reconcile line totals and invoice rounding separately
Sum the explicitly chosen line outputs, for example =SUM(C2:C20), and verify that required rows contain numerical values. SUM can omit text in a referenced range, so a plausible total is not proof that imported prices were recognized correctly. Keep zero-priced items distinct from missing inputs. If you use rounding at the line level, compare that total with a separately rounded order-level total to understand why they might differ.
Display currency consistently but retain an explicit currency label in the sheet. Formatting a number as dollars does not convert pounds, Canadian dollars or Australian dollars. Tax, shipping, capped coupons and merchant-specific allocation rules are separate assumptions unless you implement them explicitly. Reconcile the sheet with the actual eligible items and receipt: a simple percentage formula does not reproduce every retailer's checkout procedure or establish the amount ultimately debited.
Cross-check supported cases with the Discount Calculator
For the stacked 160 example, choose successive discounts on NumUtility and enter 20 and 10 as whole-number percentages. Confirm final 115.20, saved 44.80 and effective discount 28%. For the single 84.50 example, compare the unrounded formula relationship and the site's two-decimal final display, allowing for the policy used to reconcile saving lines. Mode changes load example inputs, so recheck each field when switching cases.
Keep a small test set in the workbook: no discount leaves the original unchanged, 100% off gives zero forward, a reverse 100% case is rejected, and invalid or missing rates do not become valid zeros. Save the formulas, input convention and rounding policy with the results. This makes the workbook an auditable calculation record and gives later users enough information to distinguish an arithmetic error from a different promotion or invoice assumption.