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. |
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. |
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 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 . |
||||||||||||||
|
|||||||||||||||
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 |
||||||||||||||
4. |
Take a moment to examine the table in Figure 3.19 for a list of logical operator symbols and their definitions.
|
||||||||||||||
5. |
Press to move the insertion point to the Value_if_true box, and then type "OK" |
||||||||||||||
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. |
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. |
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. |
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 . |
2. |
From the Edit menu, click Replace. In the Find and Replace dialog box, in the Find what box, type Mary |
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 |
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