Excel Practice
Lessons Lesson 20

Percentages: Share, Change and Discount

About this lesson

A percentage in Excel is a plain fraction. A share is the part divided by the whole. A change is the difference divided by the old value. The percent sign is only a cell format.

By the end you can

  • work out a share of a total with a locked reference.
  • work out a percentage change with the brackets in the right place.
  • apply a discount or an increase in one multiplication.

Practises ROUND SUM

The idea

There is no percentage function, because there is nothing for one to do. A share of total is the part divided by the whole. A change is the difference divided by the starting value. A discount is the number times (1 minus the rate). Each one is division or multiplication. The only thing that goes wrong is the order of operations. (new - old) / old needs its brackets. Without them, Excel divides first.

The mistake with the share is the dollar sign. The whole is one cell. When the formula fills down, the reference to it must not move. Four shares that add up to more or less than 100% are four formulas whose total moved.

The mistake to watch for

Two mistakes, both silent. A change written as new minus old divided by old, without brackets, divides first. It returns a large number that is not a percentage of anything. And a share of total whose total is not locked moves when it is filled. Then the shares no longer add up to 100%. Check the brackets. Then check that the shares add up to 1.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In D2, work out the North's share of this year's total: its figure divided by the total in C6. Lock the total so the formula can be filled down.

To begin, type it exactly:

=C2/$C$6

D2
Row A B C D E F G
1 Region Last year This year Share Change
2 North 240 288
3 South 310 279
4 East 150 180
5 West 200 213
6 Total 900 960
7
8 Discount 0.15

Every step

  1. 1

    In D2, work out the North's share of this year's total: its figure divided by the total in C6. Lock the total so the formula can be filled down. To begin, type it exactly: =C2/$C$6.

    Hint. C2 over C6, with the C6 locked.

  2. 2

    That formula was filled down to D5, where it reads =C5/$C$6. What does D5 show?

    Hint. 213 divided by the total.

  3. 3

    In E2, work out the North's change from last year as a fraction. That is the difference between the two years, divided by last year.

    Hint. Brackets round the subtraction.

  4. 4

    The South is offered the discount in B8 on this year's figure. Read =C3*(1-B8) and say what it returns.

    Hint. 279 times 0.85.

  5. 5

    In F2, write a formula with ROUND that shows the North's change as a whole-number percentage: 20, not 0.2.

    Hint. Change fraction times 100, inside ROUND.

  6. 6

    The trap. Somebody wrote the North's change without the brackets: =C2-B2/B2. Read it and say what it returns.

    Hint. What does B2/B2 come to?