Excel Percentage Formula Practice: Change, Discount and Margin
Percentages are everywhere in a spreadsheet: a discount at the till, growth against last month, the share of the total that one product brings in, a margin on what you sell. The arithmetic is simple. What goes wrong is the setup: dividing by the wrong number, typing the rate into the formula instead of keeping it in a cell, or copying a formula down and letting the total move.
These exercises start with a price and a discount rate stored in cells, move to change against a starting value and share of a fixed total, show how to round a number for display and for real, and finish with profit and margin, where the overall margin is not the average of the product margins. Each one is checked on the data in front of you and on other data, so a formula that only works today does not pass.
What these functions are for
- Apply a discount or a tax rate that lives in its own cell, so changing the rate changes every price.
- Measure growth or decline against the starting value, in percent.
- Show what share of a fixed total each item represents.
- Round an amount for what it means: to the nearest cent, up to whole boxes, or down to whole units.
- Turn unit price and cost into profit and a margin, and report the margin for the whole range correctly.
Tips before you start
- Keep the rate in a cell and refer to it. A rate typed into the formula is hidden, and it has to be edited everywhere when it changes.
- Percent change divides by the value you started from, not the new one. Say it as: how much did it move, compared with where it began?
- Lock the total with dollar signs before you copy a share formula down, or the total slides along with the rows.
- A cell formatted as a percentage still holds a decimal number. Format for display, and use ROUND when the value itself has to change.
Skills covered
- Percentages
- Absolute references
- Arithmetic
- Discounts
- Margin
- Percent change
- Profit
- ROUND
- ROUNDDOWN
- Rounding
- ROUNDUP
- Share of total
- SUM
- SUMPRODUCT
Common mistakes to watch for
- Subtracting the percentage itself
- A 15% discount means paying 85% of the price, not the price minus 15 of something. Work out the amount saved first, or multiply by what remains.
- Dividing by the new value
- Change is measured against where you started. Dividing by the new figure gives a smaller, wrong percentage that looks plausible.
- Forgetting to lock the total
- A share formula copied down moves its total with every row unless the total cell is anchored with dollar signs.
- Relying on cell formatting to round
- Formatting changes what you see, not what a later formula uses. When the value must be rounded, round it in the formula.
- Averaging the margins
- Each product's margin carries a different weight. The overall margin comes from the total profit and the total revenue, not from the average of the individual margins.
Practice exercises, in a sensible order
Sale Price After a Percentage Discount
Use a percentage stored in a cell inside a formula, to calculate a discounted price and the amount saved.
Percent Change and Share of Total
Work out a percentage change against the starting value, and a share of a total by dividing by a fixed total cell.
Rounding Prices and Whole Boxes
Round a value for display and for real, and pick ROUND, ROUNDUP or ROUNDDOWN depending on what the number means.
Profit and Margin by Product
Turn unit figures into profit and margin, and understand why an overall margin is not the average of the product margins.