7.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 be complex enough to use advanced statistical functions to spot trends and make predictions . But in all cases, these formulas share the same basic characteristics. Here are a few points to note:

• You enter formulas 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 a displayed result was produced), you must move to the cell containing the formula and then look in the Formula bar (Section 1.3.4). 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 groups of cells when using certain functions.

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

=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 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 gives you the answer, as shown in Figure 7-1.

Figure 7-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. 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 include everything you fondly remember from high school math class, including addition (+), subtraction (-), multiplication (*), division (/), and exponentiation (^). Table 7-1 lists these ingredients, also known as arithmetic operators .

• Numbers . These 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 might 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 might 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 on Sidebar 7.3.)

Table 7-1. Arithmetic Operators

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

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

2. Percent

3. Exponents

4. Division and Multiplication

5. Addition and Subtraction

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, Excel gets confused and lets you know you need to fix things, as shown in Figure 7-2.

7.1.2. Cell References

Excel's formulas can be 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 references Excel's way of pointing to one or more cells in a worksheet.

Figure 7-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. This may not be what you wanted, though. If not, cancel the suggestion, and 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.

For example, you might 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 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 the upcoming Section 7.1.5 (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'll see the total appear in the cell that contains the formula. (If one of the cells doesn't contain numeric information, you'll 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 will also have 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 can be 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 on Section 4.3.3.)

• 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, the destination cell will have the Scientific number format. Sadly, these rules aren't spelled out anywhere , so if you don't see the result you want, the best approach is just to 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 formula cell 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, Excel doesn't copy any formatting from the source cells. Similarly, if you change a formula to refer to new source cells, Excel doesn't copy the format information from the new source cells.

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 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, like calculating a best-fit trend line.

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)

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, simply enter the function name, followed by parentheses. Then inside the parentheses, put all the information the function needs in order to perform its calculations.

In the case of the COMBIN( ) function, two pieces of information, or arguments , are required. The first is the number of items in the set (the 52-card deck), and the second is the number of items you are 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 you've typed this formula into a cell, the result (2598960) will appear 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.

COMBIN(number_in_set, number_chosen)

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 tooltip a 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 it matches a known 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 is entered into cell B2, the formula would become:

=COMBIN(52,B2)

More complex formulas combine functions, cell references, and numbers. You might even use multiple functions at once. For example, 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.

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 might 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 is known as the union operator . You can add spaces before or after a comma, but Excel will just remove them.

• The colon (:) separates the top-left and bottom-right corners of a block of cells . 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 known as the range operator by far the most powerful way to select multiple cells. You can add spaces before or after a comma, but Excel will just remove them.

• 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 on both ranges). The space is technically known as the intersection operator , and it's not used terribly often.

You can't enter ranges directly into formulas that just use the simple operators. For example, the formula =A1:B1+5 , won't work. Instead, you need to use ranges with functions. 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 must enter its name, an open parenthesis, the cell range you want to add up, and then a closed parenthesis.

Here's how you might 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 is shown in Figure 7-3. 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 7-3. Top : 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. Here's the formula as it's being entered into the cell. Note that when you type the name of a function that Excel recognizes, it automatically displays a tooltip 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) in a blue border. Bottom : Hitting Enter completes the formula and displays the result.

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 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, Excel won't be able to evaluate the formula, and it reminds you with an error message.

Excel alerts you about formula errors by using special error codes which begin with the number sign (#), as depicted in Figure 7-4. 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 7-4. If Excel spots an error, it'll insert 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, or you can tell Excel to ignore the error, in which case the error code remains but the green triangle disappears.

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

Table 7-2. Excel's Error Codes

=B1+10

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). 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 content as cell A4. On the other hand, if these cells contain different values (say 2 and 3) 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 might 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.

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

Table 7-3. Logical Operators

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, you'll 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, the second argument is displayed in the cell; if the condition is false, the third argument is displayed.

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 the text "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. Literal numbers, on the hand, you can enter directly into a formula.

---

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

=A1/A2

This formula will cause a divide-by-zero error if A2 contains a 0 value. Excel will then display 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 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.

Practical examples of conditional logic abound in Chapter 11 and Chapter 12.