Chapter 10: Using Formulas for Common Mathematical Operations
10
FIGURE 10.8
Applying a percent increase and decrease using a simple formula
To increase a number by a percentage amount, multiply the original amount by 1 plus the
percent of increase. In the example in Figure 10.8, Product A is getting a 10% increase. So,
we first add 1 to the 10%. This gives us 110%. We then multiply the original price of 100 by
110%. This calculates to the new price of 110.
To decrease a number by a percentage amount, multiply the original amount by 1, which is
the percent discount. In the example in Figure 10.8, Customer A is getting a 20% discount.
So, we first subtract 20% from 1. This gives us 80%. We then multiply the original 1000 cost
per service by 80%. This calculates to the new rate of 800.
Note the use of parentheses in the formulas. By default, Excel’s order of operations states
that multiplication must be done before addition or subtraction. But if we let that happen,
we would get an erroneous result. Wrapping the second part of the formula in parentheses
ensures that Excel performs the multiplication last.
Dealing with divide-by-zero errors
In mathematics, division by zero is impossible. One way to understand why it’s impossible
is to consider what happens when you divide a number by another.
Division is really nothing more than fancy subtraction. For example, 10 divided by 2 is the
same as starting with 10 and continuously subtracting 2 as many times as needed to get to
zero. In this case, you would need to continuously subtract 2 five times.
10 − 2 = 8
8 − 2 = 6
6 − 2 = 4
4 − 2 = 2
2 − 2 = 0So, 10/2 = 5.
Now if you tried to do this with 10 divided by 0, you would never get anywhere because
10 − 0 is 10 all day long. You’d be sitting there subtracting 0 until your calculator dies.