Page 2 of 6
Spreadsheets and percentages
The fact that it is easier to work with fractions but easier to interpret percentages causes no problem to a spreadsheet user. The reason is that nearly all spreadsheets allow you to enter a fraction but display it as a percentage. That is, if you format a cell or range to display as percentages then entering a fraction such as 0.5 will result in 50% being displayed. This can be the cause of confusion if you are not aware of what is happening.
To make what you enter the same as what is displayed, most spreadsheets will also allow you to enter a percentage using a percent sign. For example, if you enter 75% then 0.75 is stored in the cell but, as long as a percentage format applies, it will display as 75%. In this case it really is possible to forget what is stored in a cell. The danger is that you will make the mistake of including 100 in all formulas that make use of the apparent percentages. Notice that if you don’t set a percentage format then in nearly all cases a percentage will display as a decimal fraction.
Figure 1: Formatting avoids errors
In Figure 1, you can see a percentage entered with and without being formatted as a percentage. When trying to work out 50% of the value 12.34 you can see that the temptation is to enter =12.34*A2/100 because A2 looks like a percentage.
Of course it isn’t a percentage and so the correct calculation is just =12.34*A2.
- Always enter a percentage value with a trailing percentage sign
- Always format cells that contain percentages to show as percentages
- Always calculate using percentages as if they were decimal fractions
In the remainder of this book we will use the convention that in all formulas percentages are treated as decimal fractions, for example 90% as 0.9 and 5% as 0.05
Working out a simple percentage should cause no one any difficulties given a spreadsheet or even a pocket calculator. What is slightly more difficult is to work out is the result of increasing or decreasing a value by a given percentage is.
As long as you think about it in the right way even this isn’t difficult. If a value increases by I% then you have the original value plus I% of it where I% is written as a decimal fraction:
value + value *I
or more simply
The quantity (1+I) occurs so often in financial functions that it is worth remembering it as what you multiply to increase a given value by I%. In the same way to decrease a given value by I% you multiply by (1-I). See Figure 2.
Figure 2: Multiplying to increase and decrease by I%
For example, if you are given a 40% discount, that is the price is decreased by 40% of its original value, then the discounted price is given by multiplying by (1-0.4). If you have to pay a 15% surcharge then the surcharged price is found by multiplying by (1+0.15).