Chapter Four: Introducing Excel Formulas
A worksheet is a set of cells
aligned in rows and columns. The cell content can be a statement containing a
text message, a number, or a date.
In Column A Row 1: (Cell A1) the
content of A1 is a number with the value 3.
In
Column B Row 1: (Cell B1) the content of B1 is a number with the value 5.
In
Column A Row 2: (Cell A2) the content of A2 is a label “Text
Message.”
In Column B Row 2: (Cell B2)
the content of B2 is a number, 37333, displayed as a date. Excel assigns dates
by counting the number of days since January 1, 1900. Because Excel stores each
date as a number, you can add and subtract dates or easily put them in calendar
order. To see the date, the number has to be formatted as a date.
|
|
Column A |
Column B |
Column C |
Column D |
|
Row 1 |
3 |
5 |
|
|
|
Row 2 |
Text Message |
March 18, 2002 |
|
|
Cell content can also be a formula. A formula asks a question and tells
Excel to display the answer to that question. A formula always starts with an =
sign. Think of a formula as “What
is?” followed by a question. A cell containing a formula is like a
flash card; the cell displays the result of calculating the formula. The
formula appears in the formula bar above the worksheet.
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, if you select cell C1.
When
you enter these statements or formulas in cell C1, here is what will be
displayed:
|
These
statements 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 |
|
SUM(A1:B1) |
SUM(A1:B1) |
|
=SUM(A1:B1) |
8 |
|
5+B1 |
5+B1 |
|
=5+B1 |
10 |
The =SUM(A1:B1) formula works by referencing a rectangle,
called a range. A range's shape is described by the two cell addresses in
opposite corners. For example the range (A1:C2) would include the cells A1, B1,
C1, A2, B2, and C2. The Sum instruction tells Excel to add the total of all the
cells in the range. You can create Summation formulas automatically with an
icon (S) found on the Standard toolbar. Summation is normally used
to find the total of a row, column, or range of cells, not for simple addition
of two cells. If you create a formula by clicking the Summation icon instead of
the = sign, it wastes computer power and memory.
Example of ranges: B3:C4, E2:E4, C6:E7
|
|
A |
B |
C |
D |
E |
F |
|
1 |
|
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
3 |
|
|
|
|
|
|
|
4 |
|
|
|
|
|
|
|
5 |
|
|
|
|
|
|
|
6 |
|
|
|
|
|
|
|
7 |
|
|
|
|
|
|
|
8 |
|
|
|
|
|
|
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.
Copying formulas: When a formula is copied, Excel
automatically changes the row number in any cell references if the formula is
copied into a different row. It automatically changes the column letters in any
cell references if the formula is copied into a different column. The
adjustment is equal to the number of rows or columns between the copied-into
cell and the copied-from cell. In the examples given in the tables below, the
formula in the source cell C4 was copied to cells E4 (increased column
references by 2), C6 (increased row references by 2) and E6 (increased both row
and column references by 2).
|
|
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 references like those in the table above are called
“relative (dynamic) cell references.”
You
can include dollar signs in cell references. The dollar sign does not change
the the formatting of the number, or the resulting
value of the formula. For example, both A1+B2 and $A$1 + $B$2 display exactly
the same output.
The
dollar sign in a cell reference affects what happens when the formula is copied
into another cell. The part of the
cell reference following the $ will not change.
Cell references with dollar signs
in front of just the number, C$3 or in front of just the letter, $C3, are
called “mixed cell references.” Cell references
with two dollar signs, $C$3 are called an “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 dollar sign in front of the letter or row number.
|
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 many numbers by the same constant. First set up a constant in a
cell. Then enter a formula into another cell using an absolute cell reference
in the formula. The formula can be copied and the reference to the cell
containing the constant value is unchanged. Changes to the cell containing the
constant will cause the formulas that refer to this cell to recalculate their
values.
Some common error messages: Error
messages start with a Pound sign (#).
##### If you see rail road 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 cell that no longer exists, due to a change in the
worksheet.
#NAME? Your formula contains text that Excel
doesn’t recognize. Could have many causes, usually a typo or missing
punctuation in a formula. The solution to both #REF and #NAME errors is to
click the cell that displays the error, click the button that appears, and then
click Trace Error if it appears.
CIRCULAR A
formula can not refer to the cell it is located in. For example =C4+5 can not
be used in cell C4. This will generate an error message 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 to
allow for ease of understanding.
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 |
60 |
|
|
Multiplication of Two
Cells |
= B3 * C3 |
20 |
|
|
Multiplication by a % |
= A1 * .40 |
10 |
|
|
Multiplication by a % |
= B1 * 25% |
7 |
|
|
Division by a Constant |
= C1 / 5 |
19 |
|
|
Division by a Cell |
= A2 / C2 |
3 |
|
|
Exponentiation (Squaring) |
= B3 ^ 2 |
16 |
|
|
Exponentiation (Cubing) |
= A3 ^ 3 |
27 |
|
|
Square Roots |
=SQRT(A1) |
5 |
|
|
Square Roots |
= A1 ^ 0.5 |
5 |
|
|
Cube Roots |
= B1 ^ (1/3) |
3.036589 |
|
|
Increasing by a Percentage
(4%) |
= A1 + (A1 * .04) |
26 |
|
|
Increasing by a Percentage
(4%) |
= A1 * 1.04 |
26 |
|
|
Increasing by a Percentage
(4%) |
= A1 + (A1 * 4%) |
26 |
|
|
Decreasing by a Percentage
(8%) |
= A1 - (A1 *.08) |
23 |
|
|
Decreasing by a Percentage
(8%) |
= A1 *.92 |
23 |
|
|
Decreasing by a Percentage
(8%) |
= A1 – (A1 * 8%) |
23 |
|
|
Calculate a Percentage
(Part/Sum) |
=A3 / $D$3 |
25% (Format as a %) |
|
|
Average of a Column |
= AVG (B1:B3) |
41 |
|
|
Average of a Row |
= AVG (A3:C3) |
4 |
|
|
Average of a Range |
= AVG (B1:C2) |
57 |
|
|
Formula referring to a
cell in another worksheet |
= Sheet2!C4 |
The contents of cell C4 in Sheet2. |
|