Building Basic Formulas
This chapter is from the book
This chapter is from the book
This chapter is from the book
In this chapter
Understanding Formula Basics
Understanding Operator Precedence
Controlling Worksheet Calculation
Copying and Moving Formulas
Displaying Worksheet Formulas
Converting a Formula to a Value
Working with Range Names in Formulas
Working with Links in Formulas
Formatting Numbers, Dates, and Times
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 a value, a cell reference, a range, a range name, or a function name—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 (>). Although it's unlikely that you'll ever reach it, the maximum number of characters that Excel allows within a single formula is 1,024.
NOTE
Excel won't object if you use spaces between operators and operands in your formulas. This is actually a good practice to get into because separating the elements of a formula in this way can make them much easier to read. Note, too, that Excel also accepts line breaks in formulas. This is handy if you have a very long formula because it enables you to "break up" the formula so that it appears on multiple lines. To create a line break within a formula, press Alt+Enter.
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 range-selection techniques that you learned in Chapter 1, "Getting the Most Out of Ranges." 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.
TIP
You can tell which mode Excel is currently in by looking at the status bar. On the left side, you'll see one of the following: Enter, Point, or Edit.
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.1 summarizes the mathematical operators used in arithmetic formulas.
Table 3.1 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.2 summarizes the operators you can use in comparison formulas.
Table 3.2 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. 155.
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, then entering the formula =A1&" and " &A2 returns Ben and Jerry.
For other uses of text formulas, see "Working with Text Functions," p. 133.
Using Reference Formulas
The reference operators combine two cell references or ranges to create a single joint reference. I discussed reference formulas in detail in Chapter 1, but Table 3.3 gives you a quick summary.
Table 3.3 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) |