# Objective 3. Use COUNTIF and IF Functions, and Apply Conditional Formatting

Statistical functions analyze a group of measurements. Logical functions test for specific conditions; logical functions commonly use conditional tests to determine whether specified conditionscalled criteriaare true or false.

Activity 3.5. Rotating Text in a Cell

The worksheet that you will use to confirm if the management guideline is met indicates the time slots as column headings. One way to format long column titles is to wrap the text in the cell. Another way to format long column titles is to rotate the text within the cell.

 1. Start the Excel program. Click Open , navigate to the folder where the student files for this textbook are stored, and then open e03B_Lab_Supervisors. Display the Save As dialog box, navigate to your Excel Chapter 3 folder, and then save the workbook as 3B_Lab_Supervisors_Firstname_Lastname 2. In the Name Box, type b2:q2 and press to select the column headings. From the Format menu, display the Format Cells dialog box, and then click the Alignment tab. 3. Under Orientation, in the Degrees box type 45 to increase the orientation to 45 and then compare your screen with Figure 3.15.   Figure 3.15. Alternatively, under Orientation, drag the red box at the end of the Text marker to the desired angle, or click the Degrees spin box up arrow. 4. In the Format Cells dialog box, click OK to display the text at a 45-degree angle. Select the range A2:Q2, display the Format Cells dialog box, and then click the Border tab. Under Presets, click Inside, and then click OK. Borders help identify with which columns the column titles are associated, especially when the titles are long. 5. In the Name Box, type a:q and press to select columns A through Q. From the Format menu, point to Column, and then click AutoFit Selection. Click anywhere to deselect, Save your workbook, and then compare your screen with Figure 3.16.   Figure 3.16. The selected column widths are adjusted to fit the width of the contents, which makes the data range narrower. This technique is useful when the column titles are wider than the data in the columns.

Activity 3.6. Using the COUNTIF Function

The COUNTIF function counts the number of cells within a range that meet the given conditionthe criteria that you provide. The COUNTIF function has two argumentsthe range of cells to check and the criteria.

 1. Scroll to view rows 35:42 on your screen, and notice that as you scroll, rows 1 and 2 remain visible because the Freeze Panes command has been applied. 2. Click cell B39. On the Formula Bar, click the Insert Function button . In the displayed Insert Function dialog box, click the Or select a category arrow, and then click Statistical. 3. Under Select a function, scroll down and click COUNTIF, and then click OK. In the displayed Function Arguments dialog box, in the Range box, type \$b\$3:\$q\$33 and then press . Alternatively, click the Collapse Dialog Box button, select the range b3:q33, press to apply the absolute cell reference, and then click the Expand Dialog Box button. Because you want the function to look at the same range of data for different values (the names of different supervisors), applying the absolute cell reference to the range will enable you to copy the formula into adjacent cells representing each supervisor. 4. In the Criteria box, type a39 and then compare your screen with Figure 3.17.   Figure 3.17. The formula will look at all the cells in the range and then count how many times the value in cell A39Maryoccurs. 5. Click OK. The result in cell B39 is 49. In the selected range of cells, the COUNTIF function counts the number of times Mary's name occursMary is scheduled to work 49 hours in the month of May. The function with its arguments displays on the Formula Bar. 6. With cell B39 as the active cell, drag the fill handle down through cell B42 to count the number of times that each supervisor's name occurs in the selected range, and then compare your screen with Figure 3.18.     Figure 3.18. The occurrences of the other supervisor names are counted. By using the absolute reference to the range of cells and a relative reference to the first supervisor's name, the formula can be copied instead of rewritten each time. 7. Click cell B40 and examine the formula. Notice that the range for the first argument\$B\$3:\$Q\$33did not change because of the absolute reference; but, the second argument for the criteria did change because of the relative cell reference. Click cell B41 and then B42 and examine the formula in each cell. 8. Save your workbook.

Activity 3.7. Using the If Function and Applying Conditional Formatting

A logical test is any value or expression that can be evaluated as being true or false. The IF function uses a logical test to check whether a condition is met, and then returns one value if true, and another value if false. For example, C8=100 is an expression that can be evaluated as true or false. If the value in cell C8 is equal to 100, the expression is true. If the value in cell C8 is not 100, the expression is false.

In this activity, you will use the IF function to determine whether any supervisor's proposed May schedule exceeds an average of 8 hours per day248 hours for the month.

1.

Click cell B36. Type 248 and press .

At 8 hours per day, and 31 days in the month, the maximum number of hours per month a supervisor could work is 248 (8 x 31 = 248).

2.

Click cell C39. Click the Insert Function button , click the Or select a category arrow, and then click Logical. Under Select a function, click IF. Click OK. If necessary, drag the Function Arguments dialog box to the right side of your screen.

3.

With the insertion point positioned in the Logical_test box, type b39<=\$b\$36

This logical test will look at the value in cell B39 (49 hours) and then determine if Mary's hours are less than or equal to (<=) the value in cell B36 (248 hours). The reference to B36 is absolute so the function can be copied for the other supervisors. The symbol <= is a logical operator that compares values.

4.

Take a moment to examine the table in Figure 3.19 for a list of logical operator symbols and their definitions.

Figure 3.19. Logical Operators

Logical Operator Symbol

Definition

=

Equal to

>

Greater than

<

Less than

>=

Greater than or equal to

<=

Less than or equal to

<>

Not equal to

5.

Press to move the insertion point to the Value_if_true box, and then type "OK"

If the result of the logical test is truethe number of hours worked is less than or equal to 248then Excel will display OK in the cell. The quotation marks will not display in the result. If you do not type the quotation marks, Excel will add them for you.

6.

Press to move the insertion point to the Value_if_false box, and then type "Too many hours" Compare your dialog box with Figure 3.20.

Figure 3.20.

(This item is displayed on page 806 in the print version)

If the result of the logical test is falsethe number of hours worked is greater than 248then Excel will display Too many hours in the cell.

7.

Click OK to display the result OK in cell C39. Using the fill handle, copy the formula down through cell C42. Save your workbook, and then compare your screen with Figure 3.21.

Figure 3.21.

8.

Click cell C40 and examine the formula. Notice that the first argument has been adjusted to examine cell B40 and compare it to \$B\$36. Click cell C41 and cell C42 to examine each formula to see that this pattern is followed.

Activity 3.8. Applying Conditional Formatting

Conditional formatting is a format, such as cell shading or font color, that Excel applies to cells if a specified condition is true. Conditional formatting uses a dialog box to create the logical test, and then you choose the format to apply if the test is true. In this activity, you will use conditional formatting as another way to draw attention to the condition if one of the supervisors exceeds the maximum.

 1. Select the range B39:B42, and then from the Format menu, click Conditional Formatting. 2. In the displayed Conditional Formatting dialog box, under Condition 1, in the second box click the arrow, and then click greater than. The first two parts of the condition are specifiedif the Cell Value Is greater than. 3. Press to move the insertion point to the third box. Click cell B36, and then compare your screen with Figure 3.22.   Figure 3.22. (This item is displayed on page 808 in the print version) The special format that you specify will be applied to any cell in the selected range if its value is greater than the value in cell B36, which is 248. 4. In the Conditional Formatting dialog box, click the Format button. In the displayed Format Cells dialog box, click the Font tab. Under Font style, click Bold. Click the Color arrow and in the third row, click the first colorRed. Compare your screen with Figure 3.23.   Figure 3.23.   If the logical test is true, the content of the cell will be displayed in bold and in red, as displayed in the Preview box. 5. In the Format Cells dialog box, click OK. In the Conditional Formatting dialog box, click OK. Because no supervisor worked more than 248 hours, no additional formatting is applied to any of the four selected cells. The logical test for applying the special format is not true for any of the selected cells. 6. Click cell B36, type 100 and then press . Click cell C41, click the Insert Function button , and then compare your screen with Figure 3.24.   Figure 3.24. The Function Arguments dialog box displays the function arguments applied to cell C41. If B41 (207 hours) is less than or equal to B36 (100 hours), the result of the test condition is FALSE; therefore "Too many hours" displays in C41. 7. Click Cancel to close the Function Arguments dialog box. Click cell B41, and then from the Format menu, click Conditional Formatting. The Conditional Formatting dialog box displays the conditions applied to cell B41. Because the condition is truethe cell value is greater than 100 (the value in cell B36)the bold red format is applied. 8. Click Cancel to close the Conditional Formatting dialog box. In cell B36, type 248 and press to return to the original value. Save your workbook.

Activity 3.9. Using Find and Replace to Change the Schedule

The Find and Replace feature searches the cells in a worksheetor in a selected rangefor matches and then replaces each match with a replacement value of your choice.

In this activity, you will replace all occurrences of Mary with Mark; Mark has taken over Mary's assigned hours due to her reassignment to another department at the college. Then you will check to see if this results in too many hours for Mark.

 1. In the Name Box, type b3:q33 and press . The range of cells that contains names in the schedule is selected. Restrict the find and replace operation to a specific range if there is a possibility that the name occurs elsewhere in the worksheet as it does in cell A39. 2. From the Edit menu, click Replace. In the Find and Replace dialog box, in the Find what box, type Mary Because Mary's shifts follow Mark's, it might be convenient for him to work some longer shifts. 3. Press to move the insertion point to the Replace with box. Type Mark and then click the Replace All button. Compare your screen with Figure 3.25.     Figure 3.25. The Microsoft Excel dialog box displays and informs you that 49 replacements were made. 4. Click OK. In the Find and Replace dialog box, click Close. Click any cell to deselect the range. Scroll down to view rows 35 through 42. Notice that Mary's hours are zero and Mark's hours now exceed the maximum of 248. Compare your screen with Figure 3.26.   Figure 3.26.

### Objective 4 Use a Date Function

GO! with Microsoft Office 2003 Brief (2nd Edition)
ISBN: 0131878646
EAN: 2147483647
Year: 2004
Pages: 448