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 |
2. |
In the Name Box, type b2:q2 and press |
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. |
5. |
In the Name Box, type a:q and press 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 |
|
|
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 |
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. |
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 |
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 |
||||||||||||||
|
|||||||||||||||
2. |
Click cell C39. Click the Insert Function button |
||||||||||||||
3. |
With the insertion point positioned in the Logical_test box, type b39<=$b$36 |
||||||||||||||
4. |
Take a moment to examine the table in Figure 3.19 for a list of logical operator symbols and their definitions.
|
||||||||||||||
5. |
Press |
||||||||||||||
6. |
Press 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 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. |
3. |
Press 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. |
6. |
Click cell B36, type 100 and then press 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. |
8. |
Click Cancel to close the Conditional Formatting dialog box. In cell B36, type 248 and press |
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 |
2. |
From the Edit menu, click Replace. In the Find and Replace dialog box, in the Find what box, type Mary |
3. |
Press 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 |
Windows XP
Outlook 2003
Internet Explorer
Computer Concepts
Word 2003
Chapter One. Creating Documents with Microsoft Word 2003
Chapter Two. Formatting and Organizing Text
Chapter Three. Using Graphics and Tables
Chapter Four. Using Special Document Formats, Columns, and Mail Merge
Excel 2003
Chapter One. Creating a Worksheet and Charting Data
Chapter Two. Designing Effective Worksheets
Chapter Three. Using Functions and Data Tables
Access 2003
Chapter One. Getting Started with Access Databases and Tables
Chapter Two. Sort, Filter, and Query a Database
Chapter Three. Forms and Reports
Powerpoint 2003
Chapter One. Getting Started with PowerPoint 2003
Chapter Two. Creating a Presentation
Chapter Three. Formatting a Presentation
Integrated Projects
Chapter One. Using Access Data with Other Office Applications
Chapter Two. Using Tables in Word and Excel
Chapter Three. Using Excel as a Data Source in a Mail Merge
Chapter Four. Linking Data in Office Documents
Chapter Five. Creating Presentation Content from Office Documents