Chapter Four: Introducing Excel Formulas
Exercise One: Copying Formulas with Cell References
Exercise Two: Payroll Forecast
Exercise Three: Sales Forecast
A worksheet consists of a set of cells (boxes) aligned in rows and
columns. Each cell can contain a string (letters or numbers mixed with
letters), a number, or a date. Each cell is referred to by the letter of the
column it appears in, followed by its row number.
|
|
Column A |
Column B |
Column C |
Column D |
|
Row 1 |
3 |
5 |
|
|
|
Row 2 |
Text Message |
March 18, 2002 |
|
|
In Column A
Row 1 the content of cell A1 is a number with the value 3.
In
Column B Row 1 the content of cell B1 is a number with the value 5.
In
Column A Row 2 the content of cell A2 is a label “Text
Message.”
In Column B Row 2 the content
of cell B2 is a number, 37333. The number has been formatted to appear as a
date. Why is it a number? So you can add and subtract dates or easily put them
in calendar order. Excel assigns a number to each date by counting the number
of days since January 1, 1900.
A cell can also contain a
formula. A formula is a mathematical equation. Formulas always start with an =
sign. The result of calculating the equation will appear in the cell. The
actual formula appears in the formula bar area, above the worksheet, when the
cell containing the formula is selected.
If you use a cell name (called
a cell
reference) in a
formula, Excel uses the value found in that cell to calculate the result of the
formula. For example, if you enter =A1 + 5 as a formula in cell C1 Excel will
look into cell A1. It sees a 3 in that cell, so it converts the formula into
the form = 3 + 5. The answer, 8, will be displayed in cell C1. The formula bar
will display the formula, =A1+ 5. Remember:
formulas always start with = signs!
When
you enter these statements or formulas in cell C1, here is what will be
displayed:
|
These
strings in cell
C1 |
Display
this in cell C1 |
|
These
formulas in cell
C1 |
Display
this in cell
C1 |
|
5+3 |
5+3 |
|
=5+3 |
8 |
|
B1 + 3 |
B1 + 3 |
|
=B1 + 3 |
8 |
|
A1+B1 |
A1+B1 |
|
=A1+B1 |
8 |
|
5+B1 |
5+B1 |
|
=5+B1 |
10 |
Displaying Formulas: Sometimes you may want to display the actual formulas, instead of their
results. You can
highlight each individual cell and its formula will be displayed in the formula
box located above the worksheet area. To view all of the cells as formulas,
press Ctrl + ~ (tilde). The tilde key is in the upper left corner of the
keyboard. There is a checkbox on the View tab where you can reset the worksheet
to display formulas. You can also turn the gridlines on and off from this
location.
Sometimes
you will write a formula referring to several cells. If these cells have a
rectangle shape, called a range, you can use range notation to describe them. Simply
enter the cell address from the upper left corner, followed by a colon and the
cell address from the lower right corner.
Example of ranges: B3:C4, E2:E4, C6:E7
|
|
A |
B |
C |
D |
E |
F |
|
1 |
|
|
|
|
|
|
|
2 |
|
|
|
|
Range 2 |
|
|
3 |
|
Range 1 |
Range 1 |
|
Range 2 |
|
|
4 |
|
Range 1 |
Range 1 |
|
Range 2 |
|
|
5 |
|
|
|
|
|
|
|
6 |
|
|
Range 3 |
Range 3 |
Range 3 |
|
|
7 |
|
|
Range 3 |
Range 3 |
Range 3 |
|
|
8 |
|
|
|
|
|
|
The Sum
Function Excel has some built-in shortcuts, called functions, to perform
complex tasks. The most commonly used function is =SUM. The Sum function tells
Excel to add the total of all the cells in the range. Here’s a sample, =SUM(B3:C4) You can create Sum functions with an icon (S) found
on the Standard toolbar.
Copying formulas:
|
|
C |
D |
E |
Comment |
|
4 |
= A1+B2+C3 |
|
= C1+D2+E3 |
Letters change when you
copy a formula into a different column. Numbers change when you copy a
formula into a different row. |
|
5 |
|
|
|
|
|
6 |
= A3+B4+C5 |
|
= C3+D4+E5 |
Cell addresses, like those in the table above, are called relative, or
dynamic, cell references. Cell addresses written in this form will change when
the formula is copied into other cells. The row numbers change when the formula
is copied into a different row. The letters change when the formula is copied
into a different column.
You
can write cell references in a different form. Adding dollar signs to cell
references tells Excel that you don’t want those cell references to change when
the formula is copied. The part of the cell reference following each $ will not
change.
The
dollar sign does not change the result of the formula. = A1+B2 and = $A$1 +
$B$2 will display exactly the same thing.
Cell references can have dollar
signs in front of just the number, = C$3, or in front of just the letter, = $C3.
These are called “mixed cell references.” Cell references with
dollar signs in front of the letter and number, = $C$3, are called “absolute
cell references.”
If you want to create a set of formulas that will always
refer to either one specific row: A$1, or one specific column:
$B2, use a mixed cell reference.
For a formula with a fixed reference to a specific cell, use
an absolute cell reference.
|
Mixed Cell Reference for a
row: The row number stays unchanged when the formula is copied. |
= A$1+3 |
|
Mixed Cell Reference for a
column: The column letter stays
unchanged when the formula is copied. |
= $A2 + 3 |
|
Absolute Cell Reference: The row
number and column letter are unchanged if the formula is copied. |
= 78 / $A$3 |
You can combine absolute,
mixed, and relative cell references in a single formula. In the following
table, the formula in cell C4 was copied into other cells. Observe which row
and column numbers changed and note the effect of the dollar signs.
|
|
C |
D |
E |
Comment |
|
4 |
= A$1+$B2 |
|
= C$1+$B2 |
Notice that only the A and
the 2 (in cell C4) change. Because of the $ symbol, the others don’t change. |
|
5 |
|
|
|
|
|
6 |
= A$1+$B4 |
|
= C$1+$B4 |
One use for an absolute
reference is to divide numbers in several cells by the same divisor, to find
percentages. The formula can be written once, using an absolute cell reference
to refer to the cell containing the divisor. Then it can be copied into other
cells. For example = A4 / $B$7.
Some common error messages: Error messages
start with a pound sign (#).
##### If you see railroad tracks, your column is too
narrow. Solution - widen the column.
#DIV/O! You are
dividing by an empty cell or zero. Solution - fix the formula’s denominator.
#REF! Your
formula refers to a range or cell that no longer exists, due to a change in the
worksheet.
#NAME? Your formula contains text that Excel doesn’t recognize.
This could have many causes. Usually it’s a typo or missing punctuation in a
formula.
CIRCULAR A formula cannot refer to the cell it is located in.
For example the formula =C4+5 cannot be used in cell C4. The error message
appears below the worksheet.
Examples of commonly used formulas:
There is no one right way to write a
formula; getting the correct result is what matters. Spaces are not necessary
in formulas, but were included in the examples to make them easier to read.
This table is used with the sample
formulas below:
|
|
A |
B |
C |
D |
|
1 |
25 |
28 |
95 |
|
|
2 |
42 |
91 |
14 |
|
|
3 |
3 |
4 |
5 |
|
SAMPLE
FORMULAS:
|
Type of Equation: |
Entered in Cell
D3: |
Result Displayed in D3: |
|
|
Addition of Two Cells |
= A2 + B3 |
46 |
|
|
Addition of a Constant |
= B1 + 25 |
53 |
|
|
Addition of a Row of Cells |
= SUM (A1:C1) |
148 |
|
|
Addition of a Column of
Cells |
= SUM (B1:B3) |
123 |
|
|
Addition of a |
= SUM (B1:C3) |
237 |
|
|
Addition of Scattered
Cells |
= SUM (A2,B1,C3) |
75 |
|
|
Subtraction of a Constant |
= C1 – 10 |
85 |
|
|
Subtraction of a Cell |
= B2 – B1 |
63 |
|
|
Multiplication by a
Constant |
= A3 * 20 |
||