Discount Formulas in Excel and Google Sheets: Percent Off, Sale Price and Original Price
Black Friday (27 November 2026) and Cyber Monday (30 November) are when most people suddenly need discount formulas, but the same handful works all year for price lists, quotes and shop sales. Each formula below works the same way in Excel and Google Sheets. In the examples, A2 holds the original price and B2 the discount. Comparing several stores? Our Black Friday deal tracker flags discounts that are not really discounts.
Enter the discount as a percentage
Type 25% (or 0.25) in the cell, not 25. A cell that holds 25 means 2,500%, so =A2*(1-B2) returns a large negative number. If your data has whole numbers, divide by 100 inside the formula: =A2*(1-B2/100).
Sale price after a discount
=A2*(1-B2)$80 with 25% off: 80 x 0.75 = $60. The discount amount on its own is =A2*B2 ($20).
Percent off from two prices
When a shop shows a "was" and "now" price, find the real discount with:
=(A2-C2)/A2
=1-C2/A2Both give the same answer. Was $79.99, now $59.99: 1 - 59.99/79.99 = 25.0%. Format the cell as a percentage. Always divide by the original price, not the sale price; dividing by $59.99 gives 33%, which overstates the deal.
Original price from the sale price
If you know the sale price and the discount, divide, do not multiply:
=C2/(1-B2)$45 after 40% off: 45 / 0.6 = $75. A common mistake is =C2*(1+B2), which gives $63, the wrong answer. The same logic applies to removing tax; see our percentage formulas.
Stacked discounts are not added together
"20% off, plus an extra 10% at checkout" is not 30% off. The second discount applies to the already reduced price:
=A2*(1-B2)*(1-C2)$100 becomes $80, then $72. The combined discount is 28%, which you can calculate directly:
=1-(1-B2)*(1-C2)Dollar off or percent off?
For "$25 off" versus "20% off", the dollar discount wins on cheaper items and the percentage wins on dearer ones. The break-even price is the dollar amount divided by the percentage: 25 / 20% = $125. With the price in A2, the dollar amount in B2 and the percentage in C2:
=IF(A2*C2>B2, "Percent off is better", "Dollar off is better")Spend thresholds
For "$20 off when you spend $100 or more":
=IF(A2>=100, A2-20, A2)For tiers, such as 10% off over $50 and 15% off over $100, use IFS with the biggest threshold first: =A2*(1-IFS(A2>=100,15%,A2>=50,10%,TRUE,0)). More on conditions in IF, AND, OR and IFS.
Buy one get one and multi-buys
To compare multi-buy offers, work out the effective price per item. For "buy X get Y free" with the unit price in A2:
=A2*X/(X+Y)- Buy 2 get 1 free:
=A2*2/3, a 33.3% discount per item - Buy one get one free:
=A2/2, 50% off per item, but only if you need two - Buy one get one half price:
=(A2+A2*50%)/2, which is 25% off per item, not 50%
Compare deals on unit price
Bigger packs and bigger discounts are not always cheaper. Put the price per kg, litre or item in its own column:
| A: Item | B: Original | C: % off | D: Sale price | E: Unit price | |
|---|---|---|---|---|---|
| 2 | Coffee 1 kg | $24.00 | 30% | =B2*(1-C2) = $16.80 | =D2/1 = $16.80/kg |
| 3 | Coffee 750 g | $15.00 | 15% | $12.75 | =D3/0.75 = $17.00/kg |
| 4 | Coffee 2 x 500 g | $21.00 | 20% | $16.80 | =D4/1 = $16.80/kg |
The 30% deal and the 20% two-pack deal cost the same per kilo, and the 15% deal is the most expensive. To pull out the cheapest item automatically:
=XLOOKUP(MIN(E2:E4), E2:E4, A2:A4)
=INDEX(A2:A4, MATCH(MIN(E2:E4), E2:E4, 0))Use the INDEX MATCH version for Excel 2019 and earlier. Both return the first match if two items tie. See XLOOKUP vs VLOOKUP for more on lookups.
Sales tax after the discount
In most places tax is charged on the discounted price. With the tax rate in D2:
=ROUND(A2*(1-B2)*(1+D2), 2)ROUND to 2 places keeps the answer in whole cents, so a column of totals adds up the way a receipt does.
Setting sale prices that end in .99
If you run a sale yourself, this rounds the discounted price down to the next price ending in .99, so it never ends up above the discounted amount:
=ROUNDDOWN(A2*(1-B2), 0)-0.01$66 with 10% off is $59.40, which becomes $58.99. For plain rounding to the cent, use =ROUND(A2*(1-B2), 2).
Common mistakes
- The discount typed as 25 instead of 25%.
- Percent off calculated against the sale price instead of the original.
- Stacked discounts added together instead of multiplied.
- Comparing pack prices instead of unit prices.
- Formulas copied down without locking a shared rate cell. Use $B$1 if every row uses the same discount.
Shopping the sales for gifts? Track every purchase against your budget with our Christmas budget tracker for Excel and Google Sheets.