Building Basic Formulas in Excel 2016
- 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
- From Here
This chapter is from the book
This chapter is from the book
This chapter is from the book
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’s bonus be this year? You can answer these questions, and an infinite number of others, by using Excel formulas.
All Excel formulas have the same general structure: an equal 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, which are symbols that combine the operands in some way, such as the plus sign (+) and the greater-than sign (>).
Formula Limits in Excel 2016
It’s a good idea to know the limits Excel sets on various aspects of formulas and worksheet models, even though it’s unlikely that you’ll ever bump up against these limits. Formula limits that were expanded in Excel 2007 remain the same in Excel 2016. So, in the unlikely event that you’re coming to Excel 2016 from Excel 2003 or earlier, Table 3.1 shows you the updated limits.
Table 3.1 Formula-Related Limits in Excel 2016
Object |
Excel 2016 Maximum |
Excel 2003 Maximum |
Columns |
16,384 |
1,024 |
Rows |
1,048,576 |
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. 58.
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 equal 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 it interprets certain keystrokes and mouse actions:
- When you type the equal 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 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 zero). 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. 163.
Using Text Formulas
The two types of formulas that I discussed in the previous sections, arithmetic formulas and comparison formulas, calculate or make comparisons and return values. A text formula, on the other hand, 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 aren’t 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 Chapter 7, “Working with Text Functions,” p. 139.
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). |