Building Basic Formulas in Excel 2007
This chapter is from the book
This chapter is from the book
This chapter is from the book
In this chapter
Understanding Formula Basics 55
Understanding Operator Precedence 59
Controlling Worksheet Calculation 62
Copying and Moving Formulas 64
Displaying Worksheet Formulas 67
Converting a Formula to a Value 67
Working with Range Names in Formulas 68
Working with Links in Formulas 72
Formatting Numbers, Dates, and Times 75
A worksheet is merely a lifeless collection of numbers and text until you define some kind of relationship among the various entries. You do this by creating formulas that perform calculations and produce results. This chapter takes you through some formula basics, including constructing simple arithmetic and text formulas, understanding the all-important topic of operator precedence, copying and moving worksheet formulas, and making formulas easier to build and read by taking advantage of range names.
Understanding Formula Basics
Most worksheets are created to provide answers to specific questions: What is the company's profit? Are expenses over or under budget, and by how much? What is the future value of an investment? How big will an employee bonus be this year? You can answer these questions, and an infinite variety of others, by using Excel formulas.
All Excel formulas have the same general structure: an equals sign (=) followed by one or more operands—which can be values, cell references, ranges, range names, or function names—separated by one or more operators—the symbols that combine the operands in some way, such as the plus sign (+) and the greater-than sign (>).
Formula Limits in Excel 2007
Although it's unlikely that you'll ever bump up against them, it's a good idea to know the limits that Excel sets on various aspects of formulas and worksheet models. All of these limits have been greatly expanded in Excel 2007, as Table 3.1 shows.
Table 3.1. New Formula-Related Limits in Excel 2007
|
Object |
New Maximum |
Old Maximum |
|
Columns |
16,384 |
1,024 |
|
Rows |
16,777,216 |
65,536 |
|
Formula length (characters) |
8,192 |
1,024 |
|
Function arguments |
255 |
30 |
|
Formula nesting levels |
64 |
7 |
|
Array references (rows or columns) |
Unlimited |
65,335 |
|
PivotTable columns |
16,384 |
255 |
|
PivotTable rows |
1,048,576 |
65,536 |
|
PivotTable fields |
16,384 |
255 |
|
Unique PivotField items |
1,048,576 |
32,768 |
|
→ |
Formula nesting levels refers to the number of expressions that are nested within other expressions using parentheses; see "Controlling the Order of Precedence," p. 60. |
Entering and Editing Formulas
Entering a new formula into a worksheet appears to be a straightforward process:
- Select the cell in which you want to enter the formula.
- Type an equals sign (=) to tell Excel that you're entering a formula.
- Type the formula's operands and operators.
- Press Enter to confirm the formula.
However, Excel has three different input modes that determine how Excel interprets certain keystrokes and mouse actions:
- When you type the equals sign to begin the formula, Excel goes into Enter mode, which is the mode you use to enter text (such as the formula's operands and operators).
- If you press any keyboard navigation key (such as Page Up, Page Down, or any arrow key), or if you click any other cell in the worksheet, Excel enters Point mode. This is the mode you use to select a cell or range as a formula operand. When you're in Point mode, you can use any of the standard range-selection techniques. Note that Excel returns to Enter mode as soon as you type an operator or any character.
- If you press F2, Excel enters Edit mode, which is the mode you use to make changes to the formula. For example, when you're in Edit mode, you can use the left and right arrow keys to move the cursor to another part of the formula for deleting or inserting characters. You can also enter Edit mode by clicking anywhere within the formula. Press F2 to return to Enter mode.
After you've entered a formula, you might need to return to it to make changes. Excel gives you three ways to enter Edit mode and make changes to a formula in the selected cell:
- Press F2.
- Double-click the cell.
- Use the formula bar to click anywhere inside the formula text.
Excel divides formulas into four groups: arithmetic, comparison, text, and reference. Each group has its own set of operators, and you use each group in different ways. In the next few sections, I'll show you how to use each type of formula.
Using Arithmetic Formulas
Arithmetic formulas are by far the most common type of formula. They combine numbers, cell addresses, and function results with mathematical operators to perform calculations. Table 3.2 summarizes the mathematical operators used in arithmetic formulas.
Table 3.2. The Arithmetic Operators
|
Operator |
Name |
Example |
Result |
|
+ |
Addition |
=10+5 |
15 |
|
– |
Subtraction |
=10-5 |
5 |
|
– |
Negation |
=-10 |
–10 |
|
* |
Multiplication |
=10*5 |
50 |
|
/ |
Division |
=10/5 |
2 |
|
% |
Percentage |
=10% |
0.1 |
|
^ |
Exponentiation |
=10^5 |
100000 |
Most of these operators are straightforward, but the exponentiation operator might require further explanation. The formula =x^y means that the value x is raised to the power y. For example, the formula =3^2 produces the result 9 (that is, 3*3=9). Similarly, the formula =2^4 produces 16 (that is, 2*2*2*2=16).
Using Comparison Formulas
A comparison formula is a statement that compares two or more numbers, text strings, cell contents, or function results. If the statement is true, the result of the formula is given the logical value TRUE (which is equivalent to any nonzero value). If the statement is false, the formula returns the logical value FALSE (which is equivalent to 0). Table 3.3 summarizes the operators you can use in comparison formulas.
Table 3.3. Comparison Formula Operators
|
Operator |
Name |
Example |
Result |
|
= |
Equal to |
=10=5 |
FALSE |
|
> |
Greater than |
=10>5 |
TRUE |
|
< |
Less than |
=10<5 |
FALSE |
|
>= |
Greater than or equal to |
="a">="b" |
FALSE |
|
<= |
Less than or equal to |
="a"<="b" |
TRUE |
|
<> |
Not equal to |
="a"<>"b" |
TRUE |
Comparison formulas have many uses. For example, you can determine whether to pay a salesperson a bonus by using a comparison formula to compare actual sales with a predetermined quota. If the sales are greater than the quota, the rep is awarded the bonus. You also can monitor credit collection. For example, if the amount a customer owes is more than 150 days past due, you might send the invoice to a collection agency.
|
→ |
Comparison formulas also make use of Excel's logical functions, so see "Adding Intelligence with Logical Functions," p. 167. |
Using Text Formulas
So far, I've discussed formulas that calculate or make comparisons and return values. A text formula is a formula that returns text. Text formulas use the ampersand (&) operator to work with text cells, text strings enclosed in quotation marks, and text function results.
One way to use text formulas is to concatenate text strings. For example, if you enter the formula ="soft"&"ware" into a cell, Excel displays software. Note that the quotation marks and the ampersand are not shown in the result. You also can use & to combine cells that contain text. For example, if A1 contains the text Ben and A2 contains Jerry, entering the formula =A1&" and " &A2 returns Ben and Jerry.
|
→ |
For other uses of text formulas, see "Working with Text Functions," p. 143. |
Using Reference Formulas
The reference operators combine two cell references or ranges to create a single joint reference. Table 3.4 summarizes the operators you can use in reference formulas.
Table 3.4. Reference Formula Operators
|
Operator |
Name |
Description |
|
: (colon) |
Range |
Produces a range from two cell references (for example, A1:C5) |
|
(space) |
Intersection |
Produces a range that is the intersection of two ranges (for example, A1:C5 B2:E8) |
|
, (comma) |
Union |
Produces a range that is the union of two ranges (for example, A1:C5,B2:E8) |