Section 7.1. Creating a Basic Formula


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

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 coughs up 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 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 7-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.

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

Table 7-1. Excel's Arithmetic Operators

Operator

Name

Example

Result

+

Addition

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

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


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

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



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.

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

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

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.


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

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

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 when you're feeling a bit lazy, Excel's built-in AVERAGE( ) function automatically gives you the average of any cell range.


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 Insert Functions" in Section 7.2.4.

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

7.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 7-3. This handy feature is new to Excel 2007, and it's called Formula AutoComplete.

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

Figure 7-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 theoryor, heaven forbid , trying to count out the possibilities manuallythe 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 7-2 in Section 7.1.6 for more information about Excel's error messages.
7.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 previously) 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.

7.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 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 in 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 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 7-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!

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

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

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

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


Table7-2 lists most of the error codes that Excel uses

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

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

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

########

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 if you require a certain number of fixed decimal places.


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

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



Excel 2007 for Starters. The Missing Manual
Excel 2007 for Starters. The Missing Manual
ISBN: 596528329
EAN: N/A
Year: 2004
Pages: 75

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net