Cellwise Start free

Excel guide · 4 min read

How to calculate markdown percentage in Excel

Quick answerFor one item, markdown percentage is =(original price - markdown price) / original price, formatted as a percentage: a $40 scarf marked down to $28 is 30% off. Retailers also report markdowns as a share of sales: total markdown dollars divided by sales at the price customers actually paid, which comes out higher (42.9% for that scarf).

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHI
1SKUDepartmentTypeOriginal priceMarkdown priceUnits soldUnit costMarkdown $Sales $
2Wool scarfAccessoriesPromo40283516420980
3Leather beltAccessoriesPermanent5538.52220363847
4Rain shellOuterwearPermanent1208418526481512
5Puffer vestOuterwearPromo957626414941976
6Denim jacketOuterwearPermanent110661448616924
7Cotton teeTopsPromo2217.66072641056
8Linen shirtTopsPermanent4833.62519360840
9HoodieTopsPromo604540246001800

Download this sheet as an Excel practice file (.xlsx)

Markdown % for one SKU

The sheet above is a clothing store's end-of-season markdown list: original ticket price, the reduced price, units sold at that price and unit cost. Columns H and I are helper formulas, copied down: markdown dollars =(D2-E2)*F2 and sales dollars =E2*F2.

The percentage off the original price is the number that goes on the ticket and the one buyers argue about. Subtract, divide by the ORIGINAL price, and format as a percentage (Home > %).

=(D2-E2)/D2

Result: 0.3 shows as 30% with Percent format

The scarf went from $40 to $28: 30% off. The same formula on row 6 says the denim jacket is 40% off.

Total markdown dollars with SUMPRODUCT

The percentage on a ticket tells you nothing about cost until you multiply by how many sold at that price. Markdown dollars are the price cut times the units sold, added up across every SKU. SUMPRODUCT does it in one cell without the helper column.

Count only units that actually sold at the reduced price. Units still on the shelf haven't cost you anything yet; units sold at full price before the markdown don't belong here at all.

=SUMPRODUCT((D2:D9-E2:E9)*F2:F9)

Result: 3765

$3,765 of markdowns this season. The same total as =SUM(H2:H9).

Markdown % of sales: the number retail reports use

Merchandise plans and retail reports usually express markdowns as a percentage of net sales: markdown dollars divided by the sales dollars you actually took. It's what lets you compare a $2 million department with a $200,000 one.

Expect it to look bigger than the ticket percentage. Every item in Accessories is exactly 30% off, yet Accessories markdowns are 42.9% of its sales, because the base is the reduced price, not the original. That's not an error. Just label which one you mean.

=SUM(H2:H9)/SUM(I2:I9)

Result: 0.3789632612 shows as 37.9% with Percent format

37.9% for the whole list: $3,765 of markdowns on $9,935 of sales.

By department with SUMIFS

Divide the department's markdown dollars by the department's sales. Put the department name in a cell instead of typing it, so one formula fills a summary table.

=SUMIFS(H2:H9,B2:B9,"Outerwear")/SUMIFS(I2:I9,B2:B9,"Outerwear")

Result: 0.39845874887 shows as 39.8% with Percent format

39.8% for Outerwear. Accessories comes out at 42.9% and Tops at 33.1%.

Markdowns against the original value instead

Some teams divide by sales at original price. It gives the average depth of the cut across what you sold. Use whichever your plan uses, and never compare one with the other.

=SUM(H2:H9)/SUMPRODUCT(D2:D9*F2:F9)

Result: 0.27481751825 shows as 27.5% with Percent format

27.5%: $3,765 off $13,700 of goods at original price.

Permanent vs promotional markdowns

A permanent markdown is a price cut that stays until the item is gone, usually to clear slow stock. A promotional markdown is temporary (a weekend sale) and the price goes back up. Track them in a Type column, because they mean different things: a high permanent share says the buy was too deep or the product missed, a high promo share says you are buying sales.

=SUMIFS(H2:H9,C2:C9,"Permanent")

Result: 1987

$1,987 of permanent markdowns, which is 52.8% of the $3,765 total. Promo markdowns are the other $1,778.

Gross margin after the markdown

This is where a markdown actually bites. The denim jacket costs $48. At $110 it earned a 56.4% margin; at $66 the margin is 27.3%. A 40% price cut more than halved the margin, because cost doesn't move.

For the whole list, weight by units: gross profit at the prices paid divided by sales at the prices paid. The blended margin at original prices, =SUMPRODUCT((D2:D9-G2:G9)*F2:F9)/SUMPRODUCT(D2:D9*F2:F9), was 59.6%.

=SUMPRODUCT((E2:E9-G2:G9)*F2:F9)/SUM(I2:I9)

Result: 0.44348263714 shows as 44.3% with Percent format

44.3%: the markdowns took the season's margin down by about 15 points. Compare this with your plan before approving the next round.

Why stacked discounts don't add up

"An extra 20% off already-reduced items" on a 30% markdown is not 50% off. The 20% comes off the $28 price, so the scarf sells for $22.40. Multiply the remaining shares and subtract from 1 to get the true total.

This is also why the markdown % column should always be calculated from the original price and the final selling price, not by adding the percentages from each round.

=1-(1-30%)*(1-20%)

Result: 0.44 shows as 44% with Percent format

44% off in total, not 50%.

Common mistakes

Dividing by the markdown price for the ticket percentage

Percent off is always (original - new) / original. Dividing by the new price gives 42.9% instead of 30% for the scarf.

Averaging the percentage column

A 40% cut on 14 jackets and a 20% cut on 60 tees don't average to 30%. Use markdown dollars with SUMPRODUCT, then divide.

Mixing the two definitions

Markdown % of sales and percent off original are both valid. Write which one in the header and stick to it across months.

Counting units still in stock

Markdown dollars are earned only when the unit sells. Multiply by units sold at the reduced price, not units marked down.

FAQ

What is the formula for markdown percentage?

=(original price - markdown price) / original price, formatted as a percentage. A $110 jacket reduced to $66 is (110-66)/110 = 40% off.

How is markdown % of sales calculated?

Total markdown dollars (price cut times units sold) divided by net sales at the price customers paid. It's higher than the ticket percentage because the base is the reduced price.

What's the difference between markdown and markup?

Markup is how much you add on top of cost to set the price: (price - cost) / cost. Markdown is how much you take off the price later: (original - new) / original. See the markup guide for the first one.

How do I calculate the original price from a markdown?

Divide the reduced price by 1 minus the markdown: =28/(1-30%) gives $40. Don't add 30% back to $28; that gives $36.40.

Functions used

Related guides

Updated