Introducing Excel Formulas:
© Mike Splane 2004
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. Dates are a special
case. Although you see a date, the application stores the information as a
number. It counts the days since
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.
|
|
Column A |
Column B |
Column C |
Column D |
|
Row 1 |
3 |
5 |
|
|
|
Row 2 |
Text Message |
|
|
|
The
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 type a cell name in a formula, this is called a cell reference. The formula
uses the value of the referenced cell to calculate its result. For example, if
you type =A1+5 as a formula in cell C1, Excel will look into cell A1, then
calculate the formula after converting the A1 cell reference into the value
stored in A1. In this case the value is 3, and Excel will answer the question, what is 3+5? The answer is 8, which will be displayed in 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 Control and ~ (tilde). The
tilde key is in the upper left corner of the keyboard. Or, use the Tools Menu
under Options. 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 add dollar signs to formulas to change the
way Excel changes cell references when it copies a formula. Adding a dollar sign in a cell reference
formula means: Don't change the next part of this cell reference when
copying this formula. When a formula containing a
dollar sign is copied to another cell, the part of the cell reference preceded
by the $ will not change.
Cell references with dollar signs in front of just the number, or in front of just the letter, are called “Mixed Cell References.” Use a mixed cell reference in a formula when you want to create a set of formulas that will always refer to either one specific row: A$1, or one specific column: $B2. If the formula should always use the same cell reference, add two dollar signs: $C$3. This is called an “Absolute Cell Reference.”
You
can combine absolute, mixed, and relative cell references in a single formula.
The dollar sign does not change the resulting value of the formula in any way. For
example, both = A1+B2 and = $A$1 + $B$2 display exactly the same output.
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 usually 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.
A
formula can not reference the cell it is located in; this will generate an
error message. You will see Circular followed by
a cell reference in the area below the worksheet.
Formulas
using multiple worksheets: You can write formulas that refer to cells in other
worksheets by including the worksheet name in the cell address. For example, If
you typed =’Sheet2’!C4 in sheet 1, cell A3, that cell would display the
contents of cell C4 in sheet 2. If the contents of sheet 2
cell C4 was a formula, you would see the results of the formula, not a
formula.
Order
of operations: If a formula contains multiple calculations, Excel calculates the
parts of the formula in this order: calculations in parentheses, then
percentages, then exponentiation, then multiplication and division, then addition
and subtraction, always moving from left to right. Use parentheses to force
Excel to do the calculations in the order you want. For example = 3+4*2 will
display 11, since Excel would do the multiplication first, while = (3+4)*2 will
display 14.
Advanced Formulas: Excel has a built-in set of advanced formulas and functions. To access them, click on the fx symbol to the left of the formula bar, or click on the pull down box next to the AutoSum Icon, or use the Insert Menu. Here are some commonly used functions:
SUM Example = SUM(C3:G9)
Result: Total of all numbers in the range.
AV
Result: Average of all numbers in the range.
COUNTIF Example = COUNTIF(C3:G9, “>55”)
Result: # of
cells in the range containing numbers greater than 55.
Used in counting groups of similar numbers within a set of data.
TODAY() Example = TODAY()
Result = current date.
Useful for forms that require a date.
ROUND Example = ROUND(A4/3,2)
Result if A4 = 5 then 5/3 = 1.6666 which is rounded to
1.67
IF Example = IF(A4<4, “Small number”, 40)
The cell containing this function will display either Small Number, or 40, depending on the contents of cell A4.
VLOOKUP Example = VLookUp(C4,G3:K12,3)
The cell containing this
function will display the number in the third column of the range G3 to K12, if
the contents of cell C4 match an entry in one of the cells in the first column
of the range.
IF Functions: We can use an IF Function
to select what appears in a cell, choosing between two different options. Which
option is displayed in the cell containing the IF function depends on the
result of an equation. If the conditions in the equation are met, the first
option is displayed. If not, the second option is displayed.
An IF function has 3 parts. The first part is an equation comparing a constant
and a variable. The second and third parts show the two options for Excel to
display. The variable in the equation is
always entered into a different cell from the cell containing the IF function
EXAMPLE: =
IF (A4 > 4, A4 + 2, 3) Cell A4 contains the variable, the number 4 is the constant. If our
test condition is met, (i.e. A4 > 4), Excel selects the first formula. The
cell containing the IF function will display whatever value is contained in
cell A4. Otherwise, the cell containing the IF function will show the number 3.
If
cell C4 contains the IF function, here’s what would happen as the value in cell
A4 is changed:
- If cell A4 contains a
6, Excel displays 8, (the contents of cell A4 [6] + 2 ) in cell C4
- If cell A4 contains a
2, Excel displays the number 3 in cell C4.
- If cell A4 contains a
4, Excel displays the number 3 in cell C4.
IF
functions also work with strings (Text and/or numbers in quotes): =IF
(A4>74%, ”Pass”, ”Fail”). In this example, if we
put the IF function in cell C4, then cell C4 would display either Pass or Fail,
depending on the value in cell A4.
Some examples of commonly used formulas are given below. There is no one right way to write a formula; getting the correct result is what matters. Spaces are not necessary in the formulas, but they are included below 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 |
12 |
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 Range of Cells |
=
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 |
||||