- Foundation
- About 13 min
- 5 practice questions
Percentages and everyday formulas: share, discount, profit and change
Build spreadsheet formulas for a percentage share, a discount, profit and loss and percentage change, using cell addresses so the answers update.
If something looks wrong, please tell us.
Spreadsheets are used for everyday money and marks calculations: how much of the total is spent on rent, what a shop pays after a discount, whether a sale made a profit. Each of these is a short formula. The numbers in this lesson are illustrative.
Percentage means out of one hundred
A percentage is a fraction written out of 100. To find a part as a percentage of the whole, divide the part by the whole. In a spreadsheet you can type =B2/B3 and then apply the percentage format, which multiplies by 100 and adds the % sign for you. To find a percentage of a number, multiply: 15% of 800 is =800*15%.
Formulas for everyday questions
| Question | Formula idea | Spreadsheet form (values in cells) |
|---|---|---|
| What share is part of whole? | part / whole | =B2/B3 then percentage format |
| Discount amount | price x rate | =B2*C2 with C2 = 15% |
| Price after discount | price - discount | =B2-B2*C2 |
| Profit | selling price - cost price | =C2-B2 |
| Profit percentage | profit / cost price x 100 | =(C2-B2)/B2 |
| Percentage change | (new - old) / old | =(C2-B2)/B2 |
When you type 15% into a cell, the spreadsheet stores the value 0.15 and shows it with a percent sign. This is why a formula such as =B2*C2 works when C2 shows 15%: it multiplies by 0.15. Never type 15 and then multiply by it, or the answer will be 100 times too big.
Notice the brackets in the last two. Without them, division would be done before subtraction and the answer would be wrong. Always keep the cell addresses in the formula, not typed numbers, so the answer changes when the data changes.
Rounding the answer
The function ROUND(number, digits) rounds the value itself. =ROUND(74.4,0) gives 74. Use it when the final answer must be a whole number or a fixed number of decimals.
Worked examples
Example 1. A student spent 30 rupees on stationery out of 120 rupees. Share = 30 / 120 = 0.25, shown as 25% after the percentage format.
Example 2. A bag is priced at 800 rupees with a 15% discount. Discount = 800 x 15% = 120. Price after discount = 800 - 120 = 680 rupees. The single formula =B2-B2*C2 gives the same 680 when C2 holds 15%.
Example 3. A shopkeeper buys an item for 250 rupees and sells it for 300 rupees. Profit = 300 - 250 = 50. Profit percentage = 50 / 250 = 0.2 = 20%.
Example 4. A count rises from 40 to 50. Change = (50 - 40) / 40 = 0.25, an increase of 25%. When it falls from 50 to 40 the formula gives (40 - 50) / 50 = -0.2, a fall of 20%. The two percentages differ because each uses its own starting value.
Example 5. A student scores 372 out of 500. Percentage = 372 / 500 x 100 = 74.4. =ROUND(74.4,0) gives 74.
Part divided by whole gives the share. Change divided by the old value gives the percentage change.
Key takeaways. Divide the part by the whole for a share, multiply the price by the rate for a discount, and divide the change by the old value for a percentage change. Use brackets and cell addresses, and ROUND when you need a tidy answer.
Check your understanding
-
1. A bag costs 800 rupees and a 15% discount is given. What is the price after discount?
- 120 rupees
- 780 rupees
- 680 rupees
- 920 rupees
Show answer
Answer: C. Discount = 800 x 15% = 120, so the price is 800 - 120 = 680. The figure 120 is only the discount.
-
2. An item bought for 250 rupees is sold for 300 rupees. What is the profit percentage?
- 15%
- 25%
- 50%
- 20%
Show answer
Answer: D. Profit = 50 and 50 / 250 = 0.2, which is 20%. The percentage is on the cost price, not the selling price.
-
3. A value rises from 40 to 50. What is the percentage change?
- 25% increase
- 20% increase
- 10% increase
- 50% increase
Show answer
Answer: A. Change = 10 and 10 / 40 = 0.25, an increase of 25%. Dividing by the new value would wrongly give 20%.
-
4. Why does =C2-B2/B2 usually give a wrong percentage change?
- It lacks a colon sign
- It is far too long
- Division is done first
- It uses cell addresses
Show answer
Answer: C. Division is done first, so only B2/B2 = 1 is calculated and then subtracted. Brackets are needed: =(C2-B2)/B2.
-
5. What does =ROUND(74.4,0) return?
- 74.4
- 75
- 70
- 74
Show answer
Answer: D. Rounding to 0 decimal places gives the nearest whole number, 74.
Sign in to keep track of the lessons you finish.