# Formulas and Functions

 [ LiB ]

If you only used it to store data, a spreadsheet wouldn't be much different from a word processing document. But spreadsheets have an additional function that you won't find in word processors: they perform calculations . For example, you can tell a spreadsheet that one cell should always contain the sum of the values in two other cells or the average of a column of cells . You need to understand the basics of such formulas and functions to work effectively with a spreadsheet.

#### Arithmetic Formulas

The simplest formulas involve basic arithmetic: addition, subtraction, multiplication, and division. In this section, we review how to construct formulas using these basic operations.

##### Building Simple Formulas Generate formulas using cell references and arithmetic operators (addition, subtraction, multiplication, division).

To create a very simple formula, follow these steps:

1. Type the value 1 in cell A1.

2. Type the value 1 in cell A2.

3. Type the value =A1+A2 in cell A3 and press Enter.

Figure 5.16 shows the worksheet at this point. Notice that the worksheet displays the value 2 in cell A3, whereas the formula bar shows the actual formula that you typed.

##### Figure 5.16. A worksheet with a simple formula. When you type an equals sign as the first character in a cell, that tells Excel that the cell will contain a formula instructions for a calculation. Excel displays the results of the calculation, but as the formula bar shows, it stores the actual formula.

This particular formula tells Excel that cell A3 should contain the sum of the values in cells A1 and A2. If you change one of those values, Excel automatically recalculates the formula. For example, type 4 in cell A1. As soon as you press Enter, cell A3 changes to display the new results of the formula, 5 .

Formulas can contain any combination of the basic arithmetic operators:

• for subtraction

• * for multiplication

• / for division

A formula can contain more than one of these operators, and you can use parentheses to group things together. Formulas can also refer to the results of other formulas. For example, at this point you can enter the formula =((A3-A1)*2)/A2 in cell B1. B1 will display the result of the calculation, 2 .

##### Formulas and Errors Recognize and understand standard error values associated with using formulas.

Once you start using formulas, there's the chance of making an error: telling Excel to calculate something that makes no sense. For example, enter the formula =A1/0 in any cell on your worksheet. Excel displays the result as #DIV/0! to indicate that you attempted to divide by zero, which of course is impossible .

Table 5.2 shows the error values that you might see on a worksheet.

##### Table 5.2. Excel Error Values

Value

Meaning

#####

The data is too wide to display. Make the column wider to see what's really here.

#VALUE!

A cell doesn't have an appropriate value for the entered formula. For example, if you enter =A1+A2 , but A1 contains letters instead of numbers , you get this error.

#DIV/0!

You attempted to divide by zero.

# NAME ?

Excel doesn't recognize some text in a formula. For instance, =ZZ1+ZZ2 causes this error because there is no column ZZ.

#N/A

A value is not available. This error most often happens when you're using a built-in function and you don't supply all the values that it requires.

#REF!

A cell reference doesn't point to anything. If you delete cell A1, for example, any formula that refers to A1 will return #REF! .

#NUM!

Invalid number. This error can happen when a calculation returns a result too large or too small for Excel to display.

#NULL!

A range reference doesn't return any cells. This error is usually the result of typing a cell reference incorrectly.

#### Cell Referencing

In the very first formula that you wrote, =A1+A2 , A1 and A2 are cell references: they tell Excel where to find values for the formula to use. Excel offers several different ways to create cell references.

##### Using Cell References Understand and use relative, mixed, absolute cell referencing in formulas.

The two basic types of cell reference are absolute and relative . An absolute cell reference isn't adjusted when you copy it; a relative cell reference is adjusted. Here's an example to show you the difference:

1. Enter the values 1 in cell A1, 2 in cell B1, 3 in cell A2, and 4 in cell B2.

2. Enter the formula =A1+A2 in cell A4.

3. Enter the formula =\$A\$1+\$A\$2 in cell A5.

4. Select cells A4 and A5 and press Ctrl+C to copy them.

5. Click in cell B4 and press Ctrl+V to paste the cells.

6. Press Esc to clear the selection highlight.

Figure 5.17 shows the result. Cell B4 shows the sum of B1 and B2, whereas B5 shows the sum of A1 and A2.

##### Figure 5.17. Experimenting with cell references. If you look at the new cells, B4 contains the formula B1+B2 , whereas B5 contains the formula \$A\$1+\$A\$2 . By default, Excel formulas are relative; when you copy them, Excel automatically adjusts them to refer to cells in the same relative positions . Copying the formula from A4 sideways fixed up the cell references to point to column B instead of column A.

Adding the dollar signs converts a relative formula into an absolute formula. Copying \$A\$1 anywhere still results in a reference to cell A1.

A third type of reference is the mixed reference , in which you specify one part of the formula as absolute and the other as relative. A cell reference of \$A1 always points to column A, but the row changes if you copy it up or down on the worksheet. A cell reference of A\$1 always points to row 1, but the column changes if you copy it sideways on the worksheet. Be prepared to enter simple expressions that perform basic mathematical and logical operations.

#### Working with Functions

As part of its formula support, Excel contains numerous built-in functions to make it easier to calculate things. You need to know about some of these functions to pass the exam.

##### Using Aggregate Functions Generate formulas using sum, average, minimum, maximum, count functions.

The first set of important functions are the aggregate functions so called because they give results based on aggregating the values in several cells. To see the basic aggregate functions in action, try this example:

1. Enter the values 1 , 2 , 3 , 4 , and 5 in cells A1 through A5.

2. Enter the formula =SUM(A1:A5) in cell B1.

3. Enter the formula =AVERAGE(A1:A5) in cell B2.

4. Enter the formula =MIN(A1:A5) in cell B3.

5. Enter the formula =MAX(A1:A5) in cell B4.

6. Enter the formula =COUNT(A1:A5) in cell B5.

Figure 5.18 shows the results.

##### Figure 5.18. Experimenting with aggregate functions. There are two things here that you haven't seen yet in this chapter. First, the reference A1:A5 is a cell range reference : It refers to an entire range of cells, from A1 through A5 inclusive. Here are some other cell range references:

• B4:B17 refers to all the cells in column B from row 4 to row 17.

• A3:J3 refers to all the cells in the third row from column A through column J.

• A2:D5 refers to a rectangular area with A2 at its upper-left corner and D5 at its lower-right corner.

The other new parts of the formulas in this exercise are the aggregate functions SUM , AVERAGE , MIN , MAX , and COUNT . They do just what you'd expect: calculate the sum, average, minimum value, maximum value, and number of values in a range. So you might read =MAX(A1:A5) as "return the maximum value out of all the values found anywhere in the range from A1 to A5."

##### Using Logical Functions Generate formulas using the logical function IF (yielding one of two specific values).

You should also know how to use the logical function IF . The IF function lets you return one of two values depending on a condition. To see how it works, try this example:

1. Enter the value 1 in cell A1.

2. Enter the formula =IF(A1=1, "Good", "Bad") in cell B2. Note that cell B2 displays the value Good .

3. Now change the value in cell A1 to 2 . Cell B2 displays Bad , as shown in Figure 5.19.

##### Figure 5.19. Using a logical formula. The IF function requires three pieces of information, separated by commas. The first is a condition that can be either true or false. In this case, the condition is A1=1 ; that's a true statement when A1 contains the value 1 and a false statement otherwise . The second part of the IF function is a value to return if the condition is true, and the third part is a value to return if the condition is false. So you might read the formula =IF(A1=1, "Good", "Bad") as "return the value Good if cell A1 contains 1 and the value Bad otherwise."

 [ LiB ] ICDL Exam Cram 2
ISBN: 0789730928
EAN: 2147483647
Year: 2006
Pages: 142