Section 8.1. Creating a Basic Formula

8.1. Creating a Basic Formula

First things first: what exactly do formulas do in Excel? A formula is 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 sum up a column of numbers , or they may incorporate advanced statistical functions to spot trends and make predictions . But in all cases, all formulas share the same basic characteristics:

• You enter each formula into a single cell.

• Excel calculates the result of a formula every time you open a spreadsheet or change the data a formula uses.

• Formula results are usually numbers, although you can create formulas that have text or Boolean (true or false) results.

• To view any formula (for example, to gain some insight into how Excel produced a displayed result), you have to move to the cell containing the formula, and then look in the formula bar (see Figure 8-1). The formula bar also doubles as a handy tool for editing your formulas.

• Formulas can evaluate a combination of numbers you input (useful when you want to use Excel as a handy calculator) or, more powerfully, the contents of other cells. Formulas can even process an entire group of cells when using certain functions.

One of the simplest formulas you can create is this one:

=1+1

The equal sign's how you tell Excel that you're entering a formula (as opposed to a string of text or numbers). The formula that follows is what you want Excel to calculate. Note that the formula doesn't include the result . When creating a formula in Excel, you write the question, and then Excel coughs up the answer, as shown in Figure 8-1. Figure 8-1. Top: This simple formula begins its life when you enter it into a cell. The checkmark and X buttons to the left of the formula bar let you quickly complete or cancel, respectively, your formula. Bottom: Or you can press Enter, and Excel displays the result in the cell. The formula bar always displays the complete formula (=1+1). In formula lingo, this particular example consists of two literal values (1 and 1) and one arithmetic operator (+).

All formulas use some combination of the following ingredients :

• The equal sign (=). Every formula must begin with the equal sign. It signals to Excel that the cell contains a formula, not just ordinary text.

• The simple operators . These ingredients include everything you fondly remember from high school math class, including addition (+), subtraction (-), multiplication (*), division (/), and exponentiation (^). Table 8-1 lists these ingredients, also known as arithmetic operators .

• Numbers . These ingredients are known as constants or literal values , because they never change (unless you edit the formula).

• Cell references . These references point to another cell, or a range of cells, that you need data from in order to perform a calculation. For example, say you have a list of 10 numbers. A formula in the cell beneath this list may refer to all 10 of the cells above it in order to calculate their average.

• Functions . Functions are specialized formulas built into Excel that let you perform a wide range of calculations. For example, Excel provides dedicated functions that calculate sums and averages, standard deviations, yields, cosines and tangents, and much more. These functions, which span every field from financial accounting to trigonometry, are described over the next four chapters.

• Spaces . Excel ignores these. However, you can use them to make a formula easier to read. For example, you can write the formula =3*5 + 6*2 instead of =3*5+6*2 . (The only exception to this rule occurs with cell ranges, where spaces have a special meaning. You'll see this described in Section 13.3.)

Table 8-1. Excel's Arithmetic Operators

Operator

Name

Example

Result

+

=1+1

2

-

Subtraction

=1-1

*

Multiplication

=2*2

4

/

Division

=4/2

2

^

Exponentiation

=2^3

8

%

Percent

=20%

0.20

Note: The percentage (%) operator divides a number by 100.

8.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 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:

=10 - 8 * 7

the result, depending on your order of operations, is either 14 or46. 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:

1. Parentheses (any calculations within parentheses are always performed first)

2. Percent

3. Exponents

4. Division and Multiplication

5. Addition and Subtraction

Note: When Excel encounters formulas that contain operators of equal precedence (that is, the same order of operation priority level), it evaluates these operators from left to right. However, in basic mathematical formulas, this has no effect on the result.

For example, consider 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 control this order, 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, then Excel gets confused and lets you know you need to fix things, as shown in Figure 8-2.

Tip: Remember, when you're working with a lengthy formula, you can expand the formula bar to see several lines at a time. To do so, click the down arrow at the far right of the formula bar (to make it three lines tall), or drag the bottom edge of the formula bar to make it as many lines large as you'd like. Section 1.3.3 shows an example. Figure 8-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.

8.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, 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 in Section 8.1.4.3, 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), then use this cell reference:

=A1

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:

=A1+A2

Note: In Excel lingo, A1 and A2 are precedents , which means another cell needs them to perform a calculation. Cell B1, which contains the formula, is called the dependent , because it depends on A1 and A2 to do its work. These terms become important when you need to hunt for errors in a complex calculation using Excel's error-checking tools (Section 13.5).

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 in Section 8.1.5.

Note: This chapter focuses on how to perform calculations using cells that contain ordinary numbers. Excel also lets you manipulate other types of content in a formula, like text and dates. You'll learn more about these topics in Chapter 11.
 GEM IN THE ROUGH Excel As a Pocket Calculator Sometimes you need to calculate a value before you enter it into your worksheet. Before you reach for your pocket calculator, you may like to know that Excel lets you enter a formula in a cell, and then use the result in that same cell. This way, the formula disappears and you're left with the result of the calculated value. Start by typing your formula into the cell (for example =65*88 ). Then, press F2 to put the cell into edit mode. Next, press F9 to perform the calculation. Finally, just hit Enter to insert this value into the cell. Remember, when you use this technique, you replace your formula with the calculated value. If your calculation is based on the values of other cells, then Excel won't update the result if you change those other cells' values. That's the difference between a cell that has a value, and a cell that has a formula. Excel has a similar trick that's helpful if you want to take a whole batch of formulas (in different cells), and replace them all with values. It's the Paste Values command. To try it out, select the cells that have the formulas you want to change, copy them (Home Clipboard Copy), and then paste them somewhere in your worksheet using the Home Clipboard Paste Paste Values command. The pasted cells now have the numbers, not the formulas.

8.1.3. How Excel Formats Cells That Contain Cell References

As you learned in Chapter 5, 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:

• Excel copies only the number format to the formula cell. It ignores other details, like fonts, fill colors, alignment, and so on. (Of course, you can manually copy formats using the Format Painter, as discussed in Section 6.2.)

• If your formula uses more than one cell reference, and the different cells use different number formats, Excel uses its own rules of precedence to decide which number format to use. For example, if you add a cell that uses the Currency number format with one that uses the Scientific number format, then the destination cell has the Scientific number format. Sadly, these rules aren't spelled out anywhere , so if you don't see the result you want, it's best to just set your own formatting.

• If you change the formatting of the source cells after you've entered the formula, it won't have any effect on the formula cell.

• Excel copies source cell formatting only if the cell that contains the formula uses the General number format (which is the format that all cells begin with). If you apply another number format to the cell before you enter the formula, then Excel doesn't copy any formatting from the source cells. Similarly, if you change a formula to refer to new source cells, then Excel doesn't copy the format information from the new source cells.

8.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 when you're feeling a bit lazy, Excel's built-in AVERAGE( ) function automatically gives you the average of any cell range. Even more usefully, Excel functions perform feats that you probably wouldn't have a hope of coding on your own, including complex mathematical and statistical calculations that predict trends hidden relationships in your data that you can use to make guesses or predict the future.

Tip: You can create your own Excel functions by writing a series of instructions using VBA (Visual Basic for Applications) code. Chapter 28 shows you how.

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:

=COMBIN(52,5)

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).

 UP TO SPEED Learning New Functions This book will introduce you to dozens of new functions. Sometimes you'll start off by looking at a sample formula that uses the function, but for more complex functions, start by considering the function description . The function description assigns a name to each argument. You can learn about the type of data the function requires before you start wading into an example with real numbers. For example, here's the function description for the COMBIN( ) function: COMBIN(number_in_set, number_chosen) You can tell the difference between a sample formula and the function description by the fact that the function description doesn't include the initial equal sign (=) that you need in all formulas. Sometimes a function takes an optional argument . The argument isn't required, but it may be important depending on the behavior you want. Optional arguments are always shown in square brackets. (Excel uses the same convention in its help and formula tooltips.) You'll see plenty of function descriptions in this book. You can look up function descriptions in Excel. Section 8.2.4 tells you where to look.

8.1.4.1. 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 somethingthat is, give 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 syntax or, rules for including a function within a formulais 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 8-3. This handy feature, Formula AutoComplete, is new to Excel 2007. Figure 8-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 to 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.)

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.

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 8-4.

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 theoryor, heaven forbid , trying to count out the possibilities manuallythe COMBIN( ) function handled it for you. Figure 8-4. When you type the opening parenthesis 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.

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 8-2 for more information about Excel's error messages.
 UP TO SPEED Understanding Functions Even though it's relatively easy to understand the basics behind how functions work and how to combine them in a formula, that doesn't mean you'll understand what all functions do and why you should use a particular function. If you don't already know a little probability theory, for instance, then the COMBIN( ) function may not be very useful. Excel's packed full of advanced formulas like COMBIN( ) that are tailored for statisticians, accountants , and mathematicians. Most of these you'll never need to use. In this book, functions that'll likely be useful to a nonspecialist reader are explained completely. For example, you may not know the financial term net present value , but you'll probably still be interested in using Excel's NPV( ) function to calculate the value of your investments. On the other hand, if you don't know the meaning of a complex conjugate an abstract concept used in some engineering calculationsyou won't be interested in the IMCONJUGATE( ) function. This book won't explain the math behind these more specialized functions. (In fact, properly explaining some of these concepts would require at least a whole other book!) Instead, these functions will be briefly pointed out in a note or table in the relevant chapter. This way, you can easily find these functions if they're relevant to your work and you already know the underlying math or statistical concepts that power them.

8.1.4.2. Using cell references with a function

One of the particularly powerful things about functions is that they don't necessarily need to use literal values in their arguments. They can also use cell references. For example, you could rewrite the five-card combination formula (mentioned above) so that it specifies the number of cards that'll be drawn from the deck based on a number that you've typed in somewhere else in the spreadsheet. Assuming this information's entered into cell B2, the formula would become:

=COMBIN(52,  B2  )

Building on this formula, you can calculate the probability (albeit astronomically low) of getting the exact hand you want in one draw:

=1/COMBIN(52,B2)

You could even multiply this number by 100 or use the Percent number style to see your percentage chance of getting the cards you want.

Tip: Excel gives you a detailed function reference to find functions and learn about them. Excel's information doesn't make for light reading, though; for the most part, it's in IRS-speak. You'll learn more about using this reference in Section 8.2.4.
8.1.4.3. 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 three 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.

• The space can find cells that are common to two or more different cell ranges . For example, the expression A1:A3 A1:B10 is a range that consists of only three cells: A1, A2, and A3 (because those three cells are the only ones found in both ranges). The space is technically the intersection operator , and it's not used terribly often.

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 in Section 5.1.4.4.

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 8-5. 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 including each cell reference in your formula! Figure 8-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.

Sometimes your worksheet may have 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 column by leaving out the row number. For example, the range A:A includes all the cells in column A (and, similarly, the range 2:2 includes all the cells in row 2).

The range A:A also includes any heading cells, which isn't a problem for the SUM( ) function (because it ignores text cells), but could cause problems for other functions. If you don't want to include the top cell, then you need to think carefully about what you want to do. You could create a normal range that stretches from the second cell to the last cell using the mind-blowingly big range A2:A1048576. However, this could cause a problem with older versions of Excel, which don't support as many rows. You're better off creating a table (described in Chapter 14). Tables expand automatically, updating any linked formulas.

8.1.5. 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 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:

=A1/A2

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 8-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. Figure 8-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.

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

• Help On This Error pops open Excel's online help, with a (sometimes cryptic) description of the problem and what could have caused it.

• Show Calculation Steps pops open the Evaluate Formula dialog box, where you can work your way through a complex formula one step at a time. Section 9.5 describes how this advanced feature works.

• Ignore Error tells Excel to stop bothering you about this problem, in any worksheet you create. You won't see the green triangle for this error again (although you'll still see the error code in the cell).

• Edit in Formula Bar brings you to the formula bar, where you can change the formula to fix a mistake.

• Error Checking Options opens up the Excel Options dialog box, and brings you to the section where you can configure the settings Excel uses for alerting you about errors. You can turn off background error checking , or change the color of the tiny error triangles using the settings under the Error Checking heading. (Background error checking is the feature that flags cells with tiny green triangles when the cells contain a problem.) You can also tell Excel to start paying attention to errors you previously told it ignore by clicking the Reset Ignored Errors button. Underneath that button is a section named "Error checking rules" that has a number of options that focus on specific types of errors. For example, you can choose to have Excel ignore numbers stored as text, formulas that ignore part of a range, and other situations that technically aren't errors, but usually indicate that you've done something you didn't mean to. Excel always reports genuine errors, like #VALUE! and #NAME? regardless of what choices you make in this dialog box.

Note: Sometimes a problem isn't an error, but simply the result of data that hasn't yet been entered. In this case, you can solve the problem by using a conditional error-trapping formula . This conditional formula checks if the data's present, and it performs the calculation only if it is. The next section, "Logical Operators," shows one way to use an error-trapping formula.

Table 8-2 lists the error codes that Excel uses.

Table 8-2. Excel's Error Codes

Error Code

Description

#VALUE!

You used the wrong type of data. Maybe your function expects a single value and you submitted a whole range. Or, more commonly, you might have used a function or created a simple arithmetic formula with a cell that contains text instead of numbers.

#NAME?

Excel can't find the name of the function you used. This error code usually means you misspelled a function's name, although it can indicate you used text without quotation marks or left out the empty parentheses after the function name. (You'll see how you can use text in a formula in Chapter 11.)

#NUM!

There's a problem with one of the numbers you're using. For example, this error code appears when a calculation produces a number that's too large or too small for Excel to deal with.

#DIV/0

You tried to divide by zero. This error code also appears if you try to divide by a cell that's blank, because Excel treats a blank cell as though it contains the number 0 for the purpose of simple calculations with the arithmetic operators. (Some functions, like AVERAGE( ), are a little more intelligent and ignore blank cells.)

#REF!

Your cell reference is invalid. This error most often occurs if you delete or paste over the cells you were using, or if you try to copy a cell from one worksheet to another. (For information about creating formulas that span worksheets and workbooks, refer to Chapter 13.)

#N/A

The value isn't available. This error can occur if you try to perform certain types of lookup or statistical functions that work with cell ranges. For example, if you use a function to search a range and it can't find what you need, you may get this result. (You'll learn about lookup functions in Chapter 12.) Sometimes people enter a #N/A value manually in order to tell Excel to ignore a particular cell when creating charts and graphs. The easiest way to do this is to use the NA( ) function (rather than entering the text # N/A ).

#NULL!

You used the intersection operator incorrectly. Remember, the intersection operator finds cells that two ranges share in common. This error results if there are no cells in common. Oftentimes, people use the intersection operator by accident , as the operator's just a single space character.

########

This code isn't actually an error conditionin all likelihood , Excel has successfully calculated your formula. However, the formula can't be displayed in the cell using the current number format. To solve this problem, you can widen the column, or possibly change the number format (Section 5.1.1) if you require a certain number of fixed decimal places.

Note: Chapter 13 describes a collection of Excel tools designed to help you track down the source of an error in a complex formulaespecially one where the problem isn't immediately obvious.

8.1.6. 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). 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 8-3 lists all the logical operators you can use to build formulas.

Table 8-3. Logical Operators

Operator

Name

Example

Result

=

Equal to

1=2

FALSE

>

Greater than

1>2

FALSE

<

Less than

1<2

TRUE

>=

Greater than or equal to

1>=1

TRUE

<=

Less than or equal to

1<=1

TRUE

<>

Not equal to

1<>1

FALSE

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:

=(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 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:

IF(condition, [value_if_true], [value_if_false])

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:

=IF(A1=B2, "These numbers are equal", "These numbers are not equal")

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".

Note: If you see a quotation mark in a formula, it's because that formula uses text. You must surround all literal text values with quotation marks. (Numbers are different: You can enter them directly into a formula.)

People often use the IF( ) function to prevent Excel from performing a calculation if some of the data is missing. Consider 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 from occurring, you can replace this formula with the conditional formula shown here:

=IF(A2=0, 0, A1/A2)

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

Practical examples of conditional logic abound in Chapters 12 and 13.

 TROUBLESHOOTING MOMENT Circular References One of the more aggravating problems that can occur with formulas is the infamous circular reference . A circular reference occurs when you create a formula that depends, indirectly or directly, on its own value. For example, consider what happens if you enter the following formula in cell B1. =B1+10 In order for this formula to work, Excel would need to take the current B1 value, and add 10. However, this operation changes the value of B1, which means Excel needs to recalculate the formula. If unchecked, this process would continue in an endless loop without ever producing a value. More subtle forms of circular references are possible. For example, you can create a formula in one cell that refers to a cell in another cell that refers back to the original cell. This is what's known as an indirect circular reference , but the problem is the same. Ordinarily, Excel doesn't allow circular references. When you enter a formula that contains a circular reference, Excel displays an error message and forces you to edit the formula until you've removed the circular reference. However, you can configure Excel to allow circular references by modifying the calculation settings, as described in the Formulas section of the Excel Options dialog box. In this case, Excel repeats the loop a fixed number of times, or until the value seems to settle down and stop changing. Occasionally, this technique is useful for calculating certain types of approximations in advanced formulas. In most cases, though, this approach is rather dangerous because it means you don't catch accidental circular references, which can lead to invalid data. A better approach is to write a custom function that performs a calculation in a loop using VBA (Visual Basic for Applications), as described in Chapter 28. Excel 2007[c] The Missing Manual
ISBN: 596527594
EAN: N/A
Year: 2007
Pages: 173