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

By Ali, Founder, Excel Lessons

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

Project: An Invoice Projects
The SUM Function Spreadsheet Basics
Fill Down and Fill Right Spreadsheet Basics
Relative References Cell References and Fill

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

A share is the part divided by the whole.

In `D2`, type: `=C2/$C$6`. `$C$6` is locked so it can fill down.

Row	A	B	C	D	E
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

A share is the part divided by the whole. In D2, type: =C2/$C$6. $C$6 is locked so it can fill down.

Hint. C2 over C6, with the C6 locked.
2

That formula was filled down from D2 to D5. Read =C5/$C$6 and say what it returns.

Hint. 213 divided by the total.
3

In E2, find the North's change as a fraction. Shape: =(new-old)/old. That is the difference over the old value.

Hint. Brackets round the subtraction.
4

B8 holds a 15% discount. Read =C3*(1-B8) and say what it returns.

Hint. This year times one minus the discount.
5

In F2, round the change to a whole percent. Shape: =ROUND(value*100,0).

Hint. Change fraction times 100, inside ROUND.
6

Without brackets, division happens before subtraction. Read =C2-B2/B2 and say what it returns.

Hint. What does B2/B2 come to?