Excel 2007 for Starters: The Missing Manual
7.1. Creating a Basic Formula
First things first: what exactly do formulas do in Excel? A formula is simply a series of mathematical instructions that you place in a cell in order to perform some kind of calculation. These instructions may be as simple as telling Excel to add up (sum) a column of numbers, or they may be complex enough to use advanced statistical functions to spot trends and make predictions. Here are some sample formulas written in English (the rest of this chapter is devoted to writing formulas in Excel-speak):
As you can imagine, formulas are outrageously useful, no matter what kind of spreadsheet you're creating. Say, for example, you're in sales, and you want to create a spreadsheet that keeps track of your yearly income and expenses. You can type in your sales figures for the last month and have Excel calculate and display updated year-to-date sales and profit figuresinstantly. Or, imagine that you want to create a spreadsheet to track the housing market. (You're thinking of buying a house, and you want to compare prices, mortgage rates, and loan terms on a bunch of different properties to see if you can afford anything you'd actually want to live in.) Using formulas, you can run instant "what if" calculations that show how a dozen projected monthly payments would be affected if you went with a 30-year loan over a 15-year loan. Cool, huh? In Excel, all formulasfrom dead-simple to scientist-scaryshare the same basic characteristics. Here are a few points to note:
One of the simplest formulas you can create is this one, shown in Figure 7-1: =1+1 The equal sign is how you tell Excel that you're entering a formula (as opposed to a string of text or numbers). The formula that follows the equal sign is what you want Excel to calculate. Figure 7-1. Top: This simple formula begins its life when you type it into a cell. To complete your formula and tell Excel to display the result, either press Enter, or head to the Formula bar and click the checkmark. (Clicking the X button that appears to the left of the Formula bar cancels the formula).Bottom: Even after Excel displays the formula's result (2), the Formula bar always displays the actual formula (1+1), handy if you want to tweak or just inspect it.
To complete your formula and tell Excel to display the result, you have two choices: either press Enter, or head to the Formula bar and click the checkmark. (Clicking the X button that appears to the left of the Formula bar cancels the formula). All formulas use some combination of the following ingredients:
Note: The percentage (%) operator divides a number by 100. 7.1.1. Excel's Order of Operations
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 operationsmathematician-speak for deciding which calculations to perform first when there's more than one calculation in a formula. For example, take a look at this formula: =10 - 8 * 7
Do you subtract the 8 from the 10 first, or multiply the 8 times the 7 first? Your choice matters: 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:
Note: If Excel encounters formulas that contain operators of equal precedencethat is, the same order-of-operation priority levelit evaluates these operators from left to right. (Excel could just as easily choose right, since it doesn't make a difference to basic mathematical formulas.) For example, take a look at the following formula: =5 + 2 * 2 ^ 3 - 1 To arrive at the answer of 20, Excel first performs the exponentiation (2 to the power of 3): =5 + 2 * 8 - 1
and then the multiplication: =5 + 16 - 1
and then the addition and subtraction: =20
To change the order Excel follows, you can add parentheses. For example, notice how adding parentheses affects the result in the following formulas: 5 + 2 * 2 ^ (3 - 1) = 13 (5 + 2) * 2 ^ 3 - 1 = 55 (5 + 2) * 2 ^ (3 - 1) = 28 5 + (2 * (2 ^ 3)) - 1 = 20 You must always use parentheses in pairs (one open parenthesis for every closing parenthesis). If you don't, Excel gets confusedand lets you know it's confused by displaying a dialog box (Figure 7-2). In the dialog box, you find Excel's best guess for correcting the formula. If the suggestion looks good, click Yes to accept it; otherwise, click Cancel to banish the dialog box and edit your formula by hand. Figure 7-2. Top: If you create a formula with a mismatched number of opening and closing parentheses (like this one), Excel politely lets you know by displaying the dialog box at bottom.Bottom: Excel offers to correct the formula by adding the missing parenthesis at the end. You may not want that, though. If not, cancel the suggestion, and edit your formula by hand. As you edit your formula by hand, Excel helps a little 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, so you can easily identify the expression that's enclosed between them. 7.1.2. Cell References
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, 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 referencesExcel's way of pointing to, or referencing, one or more cells in a worksheet. For example, you may 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 for vaccination shots at a travel clinic. If you use cell references, 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 will refresh the formula results automatically. 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 handful 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. Cell references also let you refer to large groups of cells by specifying a range. For example, using the cell-reference lingo you learn in the upcoming section "Specifying Cell Ranges" (Section 7.1.5), 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: =A1
In Excel-speak, this reference translates to "get the number from cell A1, and insert it in the current cell." So if you put this formula in cell B1, it displays whatever value is 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: =A1+A2
Provided both cells contain numbers, you see the total appear in the cell that contains the formula. (If one of the cells doesn't contain numeric information, you see a special error code instead that starts with a # symbol. Errors are described in more detail on Section 7.1.6.) 7.1.3. How Excel Formats Cells that Contain Cell References
As you learned in Chapter 4, 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 (i.e., the cells being referred to) use, and applies that to the cell with the formula. This means that if you add two numbers and you've formatted both with the Currency number format, 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 when it springs into action if you don't understand how it works. Here are a few points to consider:
7.1.4. Functions
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 programsyou 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 if you're feeling a bit lazy, Excel's built-in AVERAGE( ) function automatically gives you the average of any cell range. Even better, Excel functions perform feats that you probably wouldn't have a hope of coding on your own, including complex mathematical and statistical calculations, like calculating a best-fit trendline. Note: Excel provides a detailed function reference that lists all the functions you can use (and how to use them). This function reference doesn't exactly make for light reading, though; for the most part, it's written in IRS-speak. You'll learn more about using this reference in "Using the Insert Function Button to Quickly Find and Use Functions" on Section 7.2.3. 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. You may think this sounds technical, but 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: =COMBIN(52,5)
Note: Functions alone don't actually do anything in Excel. They need to be part of a formula to produce a result. For example, COMBIN( ) is the name of a function. But it actually does somethingthat is, it gives you a resultonly 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 syntaxor, rules for including a function within a formulais always similar. To use a function, simply enter the function name, followed by parentheses. Then, inside the parentheses, put all the information that particular function needs in order to perform its calculations. In the case of the COMBIN( ) function, COMBIN( ) requires two pieces of information, or arguments. The first is the number of items in the set (the 52-card deck), and the second is the number of items you're randomly selecting (in this case, 5). Most functions, like COMBIN( ), require two or three arguments. But there's no magic number. Some functions can accept many more, while a few don't need any arguments at all. Once you've typed the formula =COMBIN(52,5) 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 theoryor, heaven forbid, trying to count out the possibilities manuallythe COMBIN( ) function handled it for you, at lightning speed. When you enter a function's arguments, be sure to separate each argument with a comma. Excel guides you in this process by providing a tooltipa small pop-up windowthat presents the name of each argument as soon as it recognizes that you're entering a function. For example, when you type =COMBIN and then an open parentheses, Excel displays COMBIN(number, number_chosen) to let you know what information needs to come next. The argument you're currently entering is shown bolded in the tooltip. You don't need to worry about the capitalization of function names, as Excel automatically capitalizes whatever you type in, provided that what you type in matches a known function. 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, Excel displays an error message (#NAME?) that is Excelian for: "Hey! You got the function's name wrong." See Table 7-2 on Section 7.1.4 for more information about Excel's error messages. More complex formulas combine functions, cell references, and numbers. You may even use multiple functions at once. For example, using the five-card combination formula (mentioned above), you can calculate the probability of getting the exact hand you want in one draw: =1/COMBIN(52,5)
7.1.5. Specifying Cell Ranges
In many cases, you won'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 can be next to each other (say, a range that includes all the cells in a single column), or they can be scattered across your worksheet. Ranges are useful for computing averages, totals, and many other calculations. To group together a series of cells into a range, use one of the following three reference operators:
Note: As you might expect, Excel lets you specify ranges by selecting cells with your pointer, instead of typing in the range manually. You see this approach later in this chapter in the "Point-and-Click Formula Creation" section on Section 7.2. You can't enter ranges directly into formulas that just use the simple operators. For example, the formula =A1:B1+5 doesn't fly. But many functions are happy to accept ranges. For instance, one of Excel's most basic functions is named SUM. SUM calculates the total for a group of cells. To use the SUM( ) function, you must enter its name, an open parenthesis, the cell range you want to add up, and then a closed parenthesis. Here's how you use the SUM( ) function to add together the 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)
Figure 7-3 shows a similar SUM( ) calculation. Clearly, if you want to total a column with hundreds of values, it's far easier to specify the first and last cell using the range operator rather than to type in each individual cell reference in your formula! Tip: Sometimes your worksheet has a list with unlimited growth potential, like a list of expenses or a catalog of products. In this case, you can code your formulas to include an entire columnno matter how big it getsby leaving out the row number. For example, the range A:A selects all the cells in column A (and the range 2:2 selects all the cells in row 2). Specifying a range this way also includes any heading cells, which isn't a problem for the SUM( ) function (because it ignores text cells). If you don't want to include the top cell, just create a normal range that stretches to the last cell. For example, to select every cell in column A except for the first one, use the range A2:A65536. 7.1.6. Formula Errors
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 alerts you immediately. Moreover, 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 actually generate a valid answer. Here's an example: Figure 7-3. Top: Using a cell range as the argument in the SUM( ) function is a quick way to add up numbers in a column. When you type a function name that Excel recognizes, a tooltip appears indicating what arguments the function requires. If you're entering a range, as in this example, you'll also see that Excel highlights the cells within the range (C2, C3, and C4) using a blue border.Bottom: Hitting Enter completes the formula and displays the result. =A1/A2 If both A1 and A2 have numbers, this formula works without a hitch. But if you leave A2 blank, or if you type text into A2 instead of numbers, Excel can't evaluate the formula, and it reminds you with an error message. Excel alerts you to formula errors by using special error codes that begin with the number sign (#). As you can see in Figure 7-4, the instant Excel spots an error, it gives you options for fixing it. Figure 7-4. If Excel spots an error, it inserts a tiny green triangle into the top-left corner of the cell. When you move to the offending cell, Excel displays an exclamation mark next to it. 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. Using the menu, you can jump to Excel's online help for tips on fixing the problem.
In order to fix an error, of course, you need to track down the problem and resolve itwhich usually means correcting the formula or changing the cells the formula references. In addition to Excel's error descriptions and online help, you can use the error-code descriptions in Table 7-2 to help you pinpoint the cause of an error and fix it. Note: Sometimes an error isn't really an error, but simply the result of not typing in all the data your formula references. In this case, you can solve the problem by using a conditional error-trapping formula. This conditional formula checks to see if the data is present and only performs the calculation if it is. The next section, "Logical Operators," shows one way to use an error-trapping formula.
7.1.7. Logical Operators
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). One final category of operators that's useful when creating formulas is the 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. (For example, you can create conditions such as, "If the value of A6 is greater than 10," or, "If the sum of the numbers in Column B is equal to the sum of the numbers in Column C.") You can specify a condition using both cell references and literal values. For example, the condition A2=A4 is true if cell A2 contains the same content as cell A4. On the other hand, if these cells contain different values (say, 2 and 3), the formula generates a false value. Table 7-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 A2 contains the number 3: =(A2=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 a cell on your worksheet, 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. Their beauty lies in the fact that you can combine them with functions to create conditional logic. Conditional logic lets you perform different calculations based on different scenarios; in other words, it lets you tell Excel if this, then do that.
For example, you can use conditional logic to see how large an order is, and provide a discount if the total order cost is over $5,000. Excel evaluates the condition, meaning it determines if the condition is true or false. You can then tell Excel what to do based on that evaluation. To see conditional logic in action, you can use the SUMIF( ) function, which totals the values 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: IF(condition, [value_if_true], [value_if_false]) In English, this line of code translates to: if the condition is true, Excel displays the second argument in the cell; if the condition is false, Excel displays the third argument. Here's a formula that uses the IF( ) function: =IF(A1=B2, "These numbers are equal", "These numbers are not equal")
This formula tests to see if the value in cell A1 equals the value in cell B2. If it does (if the condition is true), you see the message "These numbers are equal" displayed in the cell. Otherwise, you see the text "These numbers are not equal." Note: If you see a quotation mark in a formula, it's because that formula uses literal text. You must surround all literal text values with quotation marks. Literal numbers, on the other hand, can be entered directly into a formula. Lots of folks use the IF( ) function to prevent Excel from performing a calculation if some of the data is missing. For example, take a look at the following formula: =A1/A2
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 error from occurring, you can replace this formula with the conditional formula shown here: =IF(A2=0, 0, A1/A2) This formula checks to see if cell A2 is empty (or contains a 0). If so, the condition is true, and the formula simply displays a 0. If cell A2 isn't empty, the condition is false, and Excel can safely go ahead and performs the division calculation A1/A2. |
Категории