Creating Formulas that Make Decisions
There will be times when you want Excel to carry out one task under certain circumstances and another task if those circumstances don t apply. To give Excel this kind of instruction, you use the IF function.
| Information about | Formula basics, page 116 |
| Information about | Arguments, page 119 |
Creating a Simple IF Function
In its simplest form, the IF function tests the value of a cell and does one thing if the test is positive (true) and another if the test is negative (false). It requires three arguments: the test, the action to perform if the test is true, and the action to perform if the test is false. You supply the arguments one after the other within the function s parentheses, separating them with commas (no spaces). Follow these steps to enter a simple IF function:
-
Select cell D4 , type the following, and then press the Enter key:
=IF(B4=0,"True","False")
Excel changes the B4 reference to blue and puts a matching blue border around cell B4 in the worksheet. Excel also checks whether the value in cell B4 is zero (the test). Because it isn t zero, Excel ignores the word True (the action to perform if the test is true) and enters the word False (the action to perform if the test is false) in cell D4.
-
Double-click cell D4 .
-
Drag through =0 to highlight it, type < 100000, and press the Enter key.
The entry in cell D4 instantly changes from False to True, because the value in cell B4 is less than one hundred thousand; that is, the test is true.
-
Now select cell D4 , and press the Delete key to clear the cell.
 |
When entering text values as arguments in a formula, you must enclose them in quotation marks. Otherwise, Excel thinks the text is a name and displays the error value #NAME? in the cell. For example, =RIGHT( Excel ,2) gives the value el, but if written =RIGHT(Excel,2), without quotation marks, it results in an error ” unless the name Excel happens to be assigned to a cell or range in the worksheet.
| |
Creating a More Complex IF Function
In the previous example, the test Excel performed was a simple evaluation of the value in a cell. However, you can also build tests that involve other functions. Suppose the last character of the job numbers in column A of the worksheet indicate whether that job is a contract (A) or subcontract (0) job, and you want to assign each job to a Contract and Subcontract category so that you can compare the contributions of the two types of jobs to Tip Top Roofing s income. Follow these steps to use an IF function to assign each job number to a category:
-
Insert a new column between columns A and B by selecting B3:B24 and clicking Columns on the Insert menu.
The first two cells in column B have been merged to accommodate the title and subtitle , so you must exclude those cells.
-
Use the Cut and Paste buttons to move the entries which now appear in C4:C8 back to their original location in B4:B8.
-
Use the Format Painter button to restore the highlighting to cells C4:C8.
-
In cell B12 , enter the heading Type of Job .
-
Select B13:B24, click Cells on the Format menu, and on the Number tab, click General , and then click OK .
-
In cell B13 , type the following, and click the Enter button:
=IF(RIGHT(A13,1)="A","Contract","Subcontract")
You have told Excel to look at the character at the right end of the value in cell A13 and to enter Contract in cell B13 if the value is A. If it is not A, Excel is to enter Subcontract . The result is shown in this graphic:
| |
Here is a list of operators you can use with the IF function:
= (equal to) > (greater than) < (less than)
< > ( unequal to) >= (greater than or equal to) <= (less than or equal to)
You can also use AND and OR to combine two or more tests. The function:
=IF(AND(B4=0,B5>0),"Yes","No")
displays Yes only if both tests are true. The function:
=IF(OR(B4=0,B5>0),"Yes","No")
displays Yes if either test is true.
| |
Using a Nested IF Function
When constructing decision-making formulas, you can use IF functions within IF functions. Called nested functions , they add another dimension to the complexity of the decisions Excel can make. Here s a quick overview:
-
In cell D12 , enter the column heading Quarter .
-
Select column D , click Cells on the Format menu, and on the Number tab of the Format Cells dialog box, doubleclick General .
-
In cell D13 , type the following formula all on one line:
=IF(MONTH(C13) < 4,1,IF(MONTH(C13) < 7,2,IF(MONTH(C13) < 10,3,4)))
-
Check the formula you entered, paying special attention to all the parentheses, and then click the Enter button.
You have told Excel to check the month component of the date in cell C13. If it is less than 4 (that is, before April), Excel is to display 1 in the corresponding cell in the Quarter column. If the month is not less than 4 but is less than 7 (that is, before July), Excel is to display 2 in the Quarter column. If it is not less than 7 but is less than 10 (that is, before October), Excel is to display 3. Otherwise, Excel is to display 4. If you have typed the formula correctly, Excel enters 1 in cell D13.
Copying Formulas with AutoFill
The IF functions you just entered are arduous to type, even for good typists. Fortunately, you don t have to enter them more than once. You can copy them using standard copy and paste techniques. However, when you want to copy the contents of a cell into the cells below or to the right, you can use a simple mouse operation called AutoFill instead of Copy and Paste. Let s explore AutoFill now:
-
Select B13 , point to the tiny square in the lower-right corner of the cell.
This square is called the fill handle .
-
When the pointer changes to a black cross, hold down the left mouse button, and drag downward to cell B24 .
When you release the mouse button, Excel copies the formula from B13 into the highlighted cells.
As you complete the AutoFill, notice the Auto Fill Options button that appears below and to the right of the range. From its drop-down list, you can refine how you want AutoFill to work. You can choose to copy the cell contents, copy just the formatting of the cell, or copy the cell contents without attaching any formatting. Since the default action is to copy the cells with formatting, we don t need to change anything right now.
-
Select cell D13 , point to the fill handle, and when the black cross appears, hold down the left mouse button, and drag downward to cell D24 .
The results are shown in this graphic:
-
Select cell B14 , and look at the formula in the formula bar.
Excel has changed the original formula
=IF(RIGHT(A13,1)="A","Contract","Subcontract")
to
=IF(RIGHT(A14,1)="A","Contract","Subcontract")
Excel changed the reference so that it refers to cell A14 as its argument, not A13.
| |
The Copy and Paste and AutoFill methods produce similar results unless the entry you are copying contains a number that can be incremented, such as those in the Quarter column, or the cell contains an entry from a custom list. If the cell contains a number that can be incremented, using AutoFill copies the entry and increments the number ”for example, 1st Quarter becomes 2nd Quarter, 3rd Quarter, and so on. If the cell contains an entry from a custom list, Excel fills the cells with other entries from that list. You define a custom list by clicking Options on the Tools menu, clicking the Custom Lists tab, selecting NEW LIST in the Custom lists box, and typing the list s entries in the List entries box. (You can also click in the Import list from cells text box, select a range containing the entries, and click the Import button to import the entries as a list.) After you click OK, you can enter the list in consecutive cells of any worksheet by typing one of the entries and dragging the fill handle. This feature is invaluable if you frequently create worksheets involving lists of the same entries, such as part numbers or employee names .
| |
Why did Excel make this change? By default, Excel uses relative references in its formulas. Relative references refer to cells by their position in relation to the cell containing the formula. So when you copied the formula in cell B13 to cell B14, Excel changed the reference from A13 to A14 ”the cell in the same row and one column to the left of the cell containing the formula. If you were to copy the formula in cell B13 to E13, Excel would change the reference from A13 to D13 so that the formula would continue to reference the cell in the same relative position.
When you don t want a reference to be copied as a relative reference, as it was in these examples, you need to use an absolute reference . Absolute references refer to cells by their fixed position in the worksheet. To make a reference absolute, you add dollar signs before its column letter and row number. For example, to change the reference C4:C9 to an absolute reference, you would enter it as $C$4:$C$9. You could then copy a formula that contained this reference anywhere on the worksheet and it would always refer to the range C4:C9.
References can also be partially relative and partially absolute. For example, $C3 has an absolute column reference and a relative row reference, and C$3 has a relative column reference and an absolute row reference.
| |
You can use the Fill command to copy entries into a range of adjacent cells. Select the cell whose contents and formats you want to copy, drag through the adjacent range, and click Fill on the Edit menu. How Excel copies the cell is determined by the shape of the selection and the command you click on the Fill submenu. For example, selecting cells below an entry and clicking Down copies the entry down a range; selecting cells to the right of an entry and clicking Right copies the entry to the right; and so on. Three related commands are also available on this submenu: Across Worksheets copies entries to the equivalent cells in a group of selected worksheets (to select the worksheets, hold down Ctrl, and click each sheet s tab); Series fills the selection with a series of values or dates; and Justify distributes the contents of the active cell evenly in the cells of the selected range.
| |
| Information about | Naming worksheets, page 136 |
EAN: N/A
Pages: 116