For computer programs and human beings alike, one of the basic challenges when it comes to reading and calculating formula results is figuring out the order of operations—mathematician-speak for deciding which calculations to perform first when there’s more than one calculation in a formula. For example, given the formula:

the result, depending on your order of operations, is either 14 or –46. Fortunately, Excel abides by what’s come to be accepted among mathematicians as the standard rules for order of operations, meaning it doesn’t necessarily process your formulas from left to right. Instead, it evaluates complex formulas piece-by-piece in this order:

For example, consider the following formula:

To arrive at the answer of 20, Excel first performs the exponentiation (2 to the power of 3):

and then the multiplication:

and then the addition and subtraction:

To control this order, you can add parentheses. For example, notice how adding parentheses affects the result in the following formulas:

You must always use parentheses in pairs (one open parenthesis for every closing parenthesis). If you don’t, then Excel gets confused and lets you know you need to fix things, as shown in Figure 15-2.

Figure 15-2.  Top: If you create a formula with a mismatched number of opening and closing parentheses (like this one), Excel won’t accept it. Bottom: Excel offers to correct the formula by adding the missing parentheses at the end. You may not want this addition, though. If not, cancel the suggestion, and edit your formula by hand. Excel helps a bit by highlighting matched sets of parentheses. For example, as you move to the opening parenthesis, Excel automatically bolds both the opening and closing parentheses in the formula bar.

Excel’s formulas are handy when you want to perform a quick calculation. But if you want to take full advantage of Excel’s power, then you’re going to want to use formulas to perform calculations on the information that’s already in your worksheet. To do that you need to use cell references—Excel’s way of pointing to one or more cells in a worksheet.

For example, say you want to calculate the cost of your Amazonian adventure holiday, based on information like the number of days your trip will last, the price of food and lodging, and the cost of vaccination shots at a travel clinic. If you use cell references, then you can enter all this information into different cells, and then write a formula that calculates a grand total. This approach buys you unlimited flexibility because you can change the cell data whenever you want (for example, turning your three-day getaway into a month-long odyssey), and Excel automatically refreshes the formula results.

Cell references are a great way to save a ton of time. They come in handy when you want to create a formula that involves a bunch of widely scattered cells whose values frequently change. For example, rather than manually adding up a bunch of subtotals to create a grand total, you can create a grand total formula that uses cell references to point to a handful of subtotal cells. They also let you refer to large groups of cells by specifying a range. For example, using the cell reference lingo you’ll learn on Using cell ranges with a function, you can specify all the cells in the first column between the 2nd and 100th rows.

Every cell reference points to another cell. For example, if you want a reference that points to cell A1 (the cell in column A, row 1), use this cell reference:

In Excel-speak, this reference translates to “get the value from cell A1, and insert it in the current cell.” So if you put this formula in cell B1, then it displays whatever value’s currently in cell A1. In other words, these two cells are now linked.

Cell references work within formulas just as regular numbers do. For example, the following formula calculates the sum of two cells, A1 and A2:

Provided both cells contain numbers, you’ll see the total appear in the cell that contains the formula. If one of the cells doesn’t contain numeric information, then you’ll see a special error code instead that starts with a # symbol. Errors are described in more detail on Formula Errors.

As you learned in Chapter 13, the way you format a cell affects how Excel displays the cell’s value. When you create a formula that references other cells, Excel attempts to simplify your life by applying automatic formatting. It reads the number format that the source cells (that is, the cells being referred to) use, and applies that to the cell with the formula. If you add two numbers and you’ve formatted both with the Currency number format, then your result also has the Currency number format. Of course, you’re always free to change the formatting of the cell after you’ve entered the formula.

Usually, Excel’s automatic formatting is quite handy. Like all automatic features, however, it’s a little annoying if you don’t understand how it works when it springs into action. Here are a few points to consider:

A good deal of Excel’s popularity is due to the collection of functions it provides. Functions are built-in, specialized algorithms that you can incorporate into your own formulas to perform powerful calculations. Functions work like miniature computer programs—you supply the data, and the function performs a calculation and gives you the result.

In some cases, functions just simplify calculations that you could probably perform on your own. For example, most people know how to calculate the average of several values, but when you’re feeling a bit lazy, Excel’s built-in AVERAGE( ) function automatically gives you the average of any cell range.

Every function provides a slightly different service. For example, one of Excel’s statistical functions is named COMBIN( ). It’s a specialized tool used by probability mathematicians to calculate the number of ways a set of items can be combined. Although this sounds technical, even ordinary folks can use COMBIN( ) to get some interesting information. You can use the COMBIN( ) function, for example, to count the number of possible combinations there are in certain games of chance.

The following formula uses COMBIN( ) to calculate how many different five-card combinations there are in a standard deck of playing cards:

Functions are always written in all-capitals. (More in a moment on what those numbers inside the parentheses are doing.) However, you don’t need to worry about the capitalization of function names because Excel automatically capitalizes the function names that you type in (provided it recognizes them).

Using a function in a formula

Functions alone don’t actually do anything in Excel. Functions need to be part of a formula to produce a result. For example, COMBIN( ) is a function name. But it actually does something—that is, give you a result—only when you’ve inserted it into a formula, like so: =COMBIN(52,5).

Whether you’re using the simplest or the most complicated function, the syntax—or, rules for including a function within a formula—is always similar. To use a function, start by entering the function name. Excel helps you out by showing a pop-up list with possible candidates as you type, as shown in Figure 15-3. This handy feature is new to Excel 2007, and it’s called Formula AutoComplete.

After you type the function name, add a pair of parentheses. Then, inside the parentheses, put all the information the function needs to perform its calculations.

Figure 15-3. After you type =COM, Excel helpfully points out that it knows only two functions that start that way: COMBIN( ) and COMPLEX( ). If your fingers are getting tired, then use the arrow keys o pick the right one out of the list, and then click Tab to pop it into your formula. (Or, you can just double-click it with the mouse.)

In the case of the COMBIN( ) function, Excel needs two pieces of information, or arguments. The first is the number of items in the set (the 52-card deck), and the second’s the number of items you’re randomly selecting (in this case, 5). Most functions, like COMBIN( ), require two or three arguments. However, some functions can accept many more, while a few don’t need any arguments at all. Once again, Formula AutoComplete guides you by telling you what arguments you need, as shown in Figure 15-4.

Figure 15-4. When you type the opening parentheses after a function name, Excel automatically displays a tooltip indicating what arguments the function requires. The argument you’re currently entering is shown bolded in the tooltip. The argument names aren’t crystal clear, but if you already know how the function works, they’re usually enough to jog your memory.

Once you’ve typed this formula into a cell, the result (2598960) appears in your worksheet. In other words, there are 2,598,960 different possible five-card combinations in any deck of cards. Rather than having to calculate this fact using probability theory—or, heaven forbid, trying to count out the possibilities manually—the COMBIN( ) function handled it for you.

Note

Even if a function doesn’t take any arguments, you still need to supply an empty set of parentheses after the function name. One example is the RAND( ) function, which generates a random fractional number. The formula =RAND( ) works fine, but if you forget the parentheses and merely enter =RAND, then Excel displays an error message (#NAME?) that’s Excelian for: “Hey! You got the function’s name wrong.” See Table 15-2 on Logical Operators for more information about Excel’s error messages.

Using cell ranges with a function

In many cases, you don’t want to refer to just a single cell, but rather a range of cells. A range is simply a grouping of multiple cells. These cells may be next to each other (say, a range that includes all the cells in a single column), or they could be scattered across your worksheet. Ranges are useful for computing averages, totals, and many other calculations.

To group together a series of cells, use one of the two following reference operators:

• The comma (,) separates more than one cell. For example, the series A1, B7, H9 is a cell range that contains three cells. The comma’s known as the union operator. You can add spaces before or after a comma, but Excel just ignores or removes them (depending on its mood).

• The colon (:) separates the top-left and bottom-right corners of a block of cells. You’re telling Excel: “Hey, use this block of cells in my formula.” For example, A1:A5 is a range that includes cells A1, A2, A3, A4, and A5. The range A2:B3 is a grid that contains cells A2, A3, B2, and B3. The colon is the range operator—by far the most powerful way to select multiple cells.

Tip

As you might expect, Excel lets you specify ranges by selecting cells with your mouse, instead of typing in the range manually. You’ll see this trick later in this chapter on Formula Shortcuts.

You can’t enter ranges directly into formulas that just use the simple operators. For example, the formula =A1:B1+5 doesn’t work, because Excel doesn’t know what to do with the range A1:B1. (Should the range be summed up? Averaged? Excel has no way of knowing.) Instead, you need to use ranges with functions that know how to use them. For instance, one of Excel’s most basic functions is named SUM( ); it calculates the total for a group of cells. To use the SUM( ) function, you enter its name, an open parenthesis, the cell range you want to add up, and then a closed parenthesis.

Here’s how you can use the SUM( ) function to add together three cells, A1, A2, and A3:

=SUM(A1,A2,A3)

And here’s a more compact syntax that performs the same calculation using the range operator:

=SUM(A1:A3)

A similar SUM( ) calculation’s shown in Figure 15-5. Clearly, if you want to total a column with hundreds of values, then it’s far easier to specify the first and last cell using the range operator rather than including each cell reference in your formula!

Figure 15-5. Using a cell range as the argument in the SUM( ) function is a quick way to add up a series of numbers in a column. Note that when you enter or edit a formula, Excel highlights all the cells that formula uses with different colored borders. In this example, you see the range of cells C2, C3, and C4 in a blue box.

If you make a syntax mistake when entering a formula (such as leaving out a function argument or including a mismatched number of parentheses), Excel lets you know right away. Moreover, like a stubborn school teacher, Excel won’t accept the formula until you’ve corrected it. It’s also possible, though, to write a perfectly legitimate formula that doesn’t return a valid answer. Here’s an example:

If both A1 and A2 have numbers, this formula works without a hitch. However, if you leave A2 blank, or if you enter text instead of numbers, then Excel can’t evaluate the formula, and it reminds you with an error message.

Excel lets you know about formula errors by using an error code that begins with the number sign (#) and ends with an exclamation point (!), as shown in Figure 15-6. In order to remove this error, you need to track down the problem and resolve it, which may mean correcting the formula or changing the cells it references.

When you click the exclamation mark icon next to an error, you see a menu of choices (as shown in Figure 15-6):

Figure 15-6. When Excel spots an error, it inserts a tiny green triangle into the cell’s top-left corner. When you move to the offending cell, Excel displays an exclamation mark icon next to it (a smart tag). Hover over the exclamation mark to view a description of the error (which appears in a tooltip), or click the exclamation icon to see a list of menu options.

Table 15-2 lists most of the error codes that Excel uses.

So far, you’ve seen the basic arithmetic operators (which are used for addition, subtraction, division, and so on) and the cell reference operators (used to specify one or more cells). There’s one final category of operators that’s useful when creating formulas: logical operators.

Logical operators let you build conditions into your formulas so the formulas produce different values depending on the value of the data they encounter. You can use a condition with cell references or literal values.

For example, the condition A2=A4 is true if cell A2 contains the same value as cell A4. On the other hand, if these cells contain different values (say 2 and 3), then the formula generates a false value. Using conditions is a stepping stone to using conditional logic. Conditional logic lets you perform different calculations based on different scenarios.

For example, you can use conditional logic to see how large an order is, and provide a discount if the total order cost’s over $5,000. Excel evaluates the condition, meaning it determines if the condition’s true or false. You can then tell Excel what to do based on that evaluation.

Table 15-3 lists all the logical operators you can use to build formulas.

You can use logical operators to build standalone formulas, but that’s not particularly useful. For example, here’s a formula that tests whether cell A1 contains the number 3:

The parentheses aren’t actually required, but they make the formula a little bit clearer, emphasizing the fact that Excel evaluates the condition first, and then displays the result in the cell. If you type this formula into the cell, then you see either the uppercase word TRUE or FALSE, depending on the content in cell A2.

On their own, logical operators don’t accomplish much. However, they really shine when you start combining them with other functions to build conditional logic. For example, you can use the SUMIF( ) function, which totals the value of certain rows, depending on whether the row matches a set condition. Or you can use the IF( ) function to determine what calculation you should perform.

The IF( ) function has the following function description:

In English, this line of code translates to: If the condition is true, display the second argument in the cell; if the condition is false, display the third argument.

Consider this formula:

This formula tests if the value in cell A1 equals the value in cell B2. If this is true, you’ll see the message “These numbers are equal” displayed in the cell. Otherwise, you’ll see “These numbers are not equal.”

People often use the IF( ) function to prevent Excel from performing a calculation if some of the data is missing. Consider the following formula:

This formula causes a divide-by-zero error if A2 contains a 0 value. Excel then displays an error code in the cell. To prevent this from occurring, you can replace this formula with the conditional formula shown here:

This formula checks if cell A2 is empty or contains a 0. If so, the condition is true, and the formula simply gives you a 0. If it isn’t, the condition is false, and Excel performs the calculation A1/A2.

Instead of entering a formula by typing it out letter-by-letter, Excel lets you create formulas by clicking the cells you want to use. For example, consider this simple formula that totals the numbers in two cells:

To build this formula by clicking, just follow these steps:

You can use a similar approach to edit formulas, although it’s slightly trickier:

The ribbon is stocked with a few buttons that make formula writing easier. To take a look, click the Formulas tab.

The most important part of the Formulas tab is the Function Library section at the left. It includes the indispensable Insert Function button, which you’ll take for a spin in the next section. It also includes many more buttons that arrange Excel’s vast catalog of functions into related categories for easier access. Figure 15-7 shows how it works.

The Function Library divides its functions into the following categories:

Excel provides more than 300 built-in functions. In order to use a function, however, you need to type its name in exactly. That means that every time you want to employ a function, you’ll need to refer to this book, call on your own incredible powers of recollection, or click over to the convenient Insert Function button.

To use the Insert Function feature, choose Formulas → Function Library → Insert Function. However, formula pros skip straight to the action by clicking the fx button that appears just to the left of the formula bar. (Or, they press the Shift+F3 shortcut key.)

No matter which approach you use, Excel displays the Insert Function dialog box (shown in Figure 15-8), which offers three ways to search for and insert any of Excel’s functions.

When you spot a function that looks promising, click it once to highlight its name. Excel then displays a brief description of the function at the bottom of the window. For more information, you can click the “Help on this function” link in the bottom-left corner of the window. To build a formula using this function, click OK.

Excel then inserts the function into the currently active cell, followed by a set of parentheses. Next, it closes the Insert Function dialog box and opens the Function Arguments dialog box (Figure 15-9).

Figure 15-9. Top: Here, the COMBIN( ) function has just been inserted via the Insert Function dialog box. Because the COMBIN( ) function requires two arguments (Number and Number_ chosen), the Function Arguments dialog box shows two text boxes. The first argument uses a literal value (52), while the second argument uses a cell reference (A1). As you enter the arguments, Excel updates the formula in the worksheet’s active cell, and displays the result of the calculation at the bottom of the Function Arguments dialog box. Bottom: If you need more room to see the worksheet and select cells, you can click the Collapse Dialog Box icon to reduce the window to a single text box. Clicking the Expand Dialog Box icon restores the window to its normal size.

Now you can finish creating your formula by using the Function Arguments dialog box, which includes a text box for every argument in the function. It also includes a help link for detailed information about the function, as shown in Figure 15-10.

To complete your formula, follow these steps:

Relative references are a true convenience since they let you create formula copies that don’t need the slightest bit of editing. But you’ve probably already realized that relative references don’t always work. For example, what if you have a value in a specific cell that you want to use in multiple calculations? You may have a currency conversion ratio that you want to use in a list of expenses. Each item in the list needs to use the same cell to perform the conversion correctly. But if you make copies of the formula using relative cell references, then you’ll find that Excel adjusts this reference automatically and the formula ends up referring to the wrong cell (and therefore the wrong conversion value).

Figure 15-13 illustrates the problem with the worksheet of student grades. In this example, the test and assignment scores aren’t all graded out of 100 possible points; each item has a different total score available (listed in row 12). In order to calculate the percentage a student earned on a test, you need to divide the test score by the total score available. This formula, for example, calculates the percentage for Edith Abbott’s performance on Test B:

Figure 15-13. In this version of the student grade book, both the tests and the assignment are graded on different scales (as listed in row 12). When you copy the Final Grade formula from the first row (cell E2) to the rows below it, Excel offsets the formula to use B13, C13, and D13—none of which provide any information. Thus a problem occurs—shown here as a divide-by-zero error. To fix this, you need to use absolute cell references.

To calculate Edith’s final grade for the class, you’d use the following formula:

Like many formulas, this one contains a mix of cells that should be relative (the individual scores in cells B2, C2, and D2) and those that should be absolute (the possible totals in cell B12, C12, and D12). As you copy this formula to subsequent rows, Excel incorrectly changes all the cell references, causing a calculation error.

Fortunately, Excel provides a perfect solution. It lets you use absolute cell references—cell references that always refer to the same cell. When you create a copy of a formula that contains an absolute cell reference, Excel doesn’t change the reference (as it does when you use relative cell references; see the previous section). To indicate that a cell reference is absolute, use the dollar sign ($) character. For example, to change B12 into an absolute reference, you would add the $ character twice, once in front of the column and once in front of the row, which changes it to $B$12.

Here’s the corrected class grade formula (for Edith) using absolute cell references:

This formula still produces the same result for the first student. However, you can now copy it correctly for use with the other students. To copy this formula into all the cells in column E, use the same procedure described in the previous section on relative cell references.

You might wonder why you need to use the $ character twice in an absolute reference (before the column letter and the row number). The reason is that Excel lets you create partially fixed references. To understand partially fixed references, it helps to remember that every cell reference consists of a column letter and a row number. With a partial fixed reference, Excel updates one component (say, the column part) but not the other (the row) when you copy the formula. If this sounds complex (or a little bizarre), consider a few examples: