Creating Formulas II

Multiplication/Division
Percentages
Order of Operations
Relative and Absolute Reference
 Creating Formulas II
In the first paper, Creating Formulas I (which I encourage you to read before diving into this one), we
discussed the two most common types of formulas for adding (summing) numbers. The practical
application for those formulas in creating budgets and most other types of worksheets is enormous.

Here in Creating Formulas II, we’ll delve deeper into the math part of Excel, using formulas to glean
some very useful information from our worksheet data. Again, like before, we’ll use a typical budget as
our example. We’ll actually be focusing on the “actuals” from a budget for the house, highlighting just
the “residential” portion of it.

Go ahead and start Excel and, with the exception of putting in the “percentage change” formulas, create
a worksheet exactly like Figure 1 (remember to format your columns correctly!).

Figure 1

Totals…

For the columnar totals (cells C13, D13 and F13), you can use the SUM( ) function (refer to the first
paper for a refresher). For the row totals (cells H5 through H13), you’ll have to use the pick and choose
method to include the figures in columns C, D and F.

Make sure your totals match mine above, otherwise you have constructed the formula wrong.

Percentage Change…

Now, a very useful formula to construct is the “percentage change” formula. It’s great for budgets,
particularly the actuals of a budget, and getting a sense of the direction of expenditures (or revenues, if
you have them constructed for that portion of the budget).

The thing to remember about percentage change is that you have to take the difference of the new figure
over the old figure and divide the result by the old figure.

Confused?

August 2003 – Stephen Groulx 2

Look at it this way:

=NewFigure – OldFigure / OldFigure

Construct the formula in cell E5 to give us the percentage change from February over January:

=D5-C5/C5

Press the Enter key and your result should look like:

The answer is correct, but it’s not what we want (Excel’s doing exactly what you told it to do).

Remember back in school they talked about order of operations in math? Same thing applies to Excel.

Multiplication and division get done before addition and subtraction. Our formula resulted in an answer
that is correct: Excel processed it correctly based on order of operation (it divided C5 by C5 first). What
we really wanted was for the difference (the result of D5-C5) to be divided by C5. To tell Excel to
process the formula this way, we need to use parentheses around the subtraction. That tells Excel to
process that part of the formula first, then do the rest, in this case the division.

So our corrected formula will look like:

=(D5-C5)/C5

Now the answer is correct:

Rather than reinvent the wheel, simply copy and paste this formula in cells E6 through E13. Go ahead
and bold E13 so it’s consistent with the other totals on that line (see Figure 2). Always remember to pay
attention to the look and feel of your worksheet: be consistent!

August 2003 – Stephen Groulx 3

 Figure 2

To do the ‘G’ percentage change column, our formula will have to include the figures in columns ‘D’
and ‘F’. Go ahead and construct your formulas. For spot checks, cell G6 should equal –6.6% and cell
G11 should equal –52.2%. If you get a different result in these cells, check your formula…including the
use of parentheses.

Percentage Of…

Another useful formula is the percentage of:

=ItemValue / TotalValue or
=SmallValue / BigValue

Don’t confuse this with the Percentage Change formula (a common mistake).

For example, let’s expand our worksheet to include percentage of. Insert a column to the left of
Percentage Change (column E). Format the cells for percentage and place your title in cell E3 (Figure 3).

Figure 3

August 2003 – Stephen Groulx 4

The formula in cell E5 should be =D5/D13. The result should be 61.6%.

Unfortunately, we can’t copy that formula into any other cell in the ‘E’ column. You can try it to see
why, and the answer in cell E6 will be #DIV/0!.

The reason we can’t copy this formula downward is actually due to a feature of Excel. That’s right, a
feature.

When you create a formula, like we did for the percentage change cells, you can copy that formula
downward and Excel adjusts the formula’s cell reference.

For example, the percentage change formula in cell F5 (Figure 3) is (D5-C5)/C5. When we copy this
formula down to cell F6, Excel adjusts the cell references in that formula to (D6-C6)/C6. Excel does the
work for us, that’s why it’s a feature.

Rule…

In other words, when we copy any formula with any cell reference downward, Excel adjusts the row
numbers in the cell references (from C5 to C6, for example). The letters do not change.

If we copy a formula with any cell reference to the left or right, Excel adjusts the column letters in the
cell references (from C5 to F5, for example). The numbers in this case do not change.

Excel calls this “adjustment” relative reference: the contents of a cell reference change relative to where
you paste it.

Whenever you copy a formula to a different cell and you get the #DIV/0! for the result, Excel has
adjusted the cell references in the formula and that’s why. Of course, there is a way to fix this (you knew
there would be, right?) so that Excel doesn’t adjust the cell references in a formula. It’s called absolute
reference.

To make a cell reference absolute, you must precede the letter and/or number with the dollar sign. You
can do either the letter or the number, or both, but be careful which method you choose. Sometimes you
may want Excel to change the letter but not the number as you copy and paste it. In our case, you would
place the dollar sign before the number only.

For our “percentage of” formula to work correctly, we need to use the dollar sign before the number,
like this:

=D5/D$13

We don’t want to use the dollar sign before the ‘5’. If we did, we would always be referring to the D5
column regardless what cell we copy this formula into in column ‘E’.

With our dollar sign before the 13, we are ensuring that we will always be referring to the contents of
D13 when we copy and paste our formula anywhere in column ‘E’.

August 2003 – Stephen Groulx 5

For example, let’s take our original formula we created in cell E5:

=D5/D$13

Copy and paste that into cell E6 and it becomes:

=D6/D$13

Copy and paste that into cell E7 and it becomes:

=D7/D$13

See the pattern of what’s happening?

Armed with this knowledge, now fill the rest of column E with our formulas (by pasting it). When you
are done, place another percentage of column to the left of H (the percentage change column). Format it
and include the title, then create your formula and copy and paste it downward.

When you’re done, your worksheet should look like this:

If it doesn’t, check your formulas for relative and absolute references. Correct them until it looks like the
worksheet above.

Summary…

To change the order of operations to perform certain things first, use parentheses. There is no limit to the
number of parentheses you can use. For example, the following formula is valid (and works!):

=(((((5+6)*2)+45)/2)-51+(((5+7)-3)/2)*2.3+(5*3))*23.7

The answer to the above formula is 186.045. If I hadn’t used the parentheses, the answer would have
been 352.55. A big difference!

August 2003 – Stephen Groulx 6

Be careful when copying and pasting formulas with cell references in them. You may get unexpected
results. If you do, it may be a simple matter of changing one or more cell references from relative to
absolute. But be cautious when making a cell reference absolute with two dollar signs (one preceding
the letter and one preceding the number).

Also, make certain you are constructing your percentage change formula correctly by using parentheses.
If you forget them, the result will be wrong!

And before finalizing your worksheet and putting real numbers in, you should also perform the “test of
ones” test. That’s where you place ones in all of your data cells (where real numbers would be placed)
and visually checking all your formulas. If you have seven items (data cells) you are checking, the total
should be seven in your formula cell.

For more on the test of ones, see the paper Testing Ones.

Good luck.

Steve

August 2003 – Stephen Groulx 7