Figure 2-23 The references are correct, but only the formulas in row 8 are doing what they should . in .NET

Encoding ECC200 in .NET Figure 2-23 The references are correct, but only the formulas in row 8 are doing what they should .

2

Your unfinished worksheet should now appear as shown in Figure 2-23: Rows 8, 9, and 10 now contain formulas for calculating an average value . If the reference style has been selected correctly as mixed (relative column, absolute row), these formulas will all refer to rows 12 through 111, and identical results will therefore appear in each of the three rows . The references are now correct but the functions in rows 9 and 10 are not . This is easy to change: 6. Select the range Q9:Z9 . We want the text Average to be replaced with Max . Press Ctrl+H to open the Find and Replace dialog box (see Figure 2-24) . Alternatively: In the Ribbon, select the Home tab and the Edit group, click the Find and Replace button, and select the Replace option . In the Find what box, enter Average, and enter Max in the Replace with box . Then click the Replace All button . DON T close the dialog box at this point .

7. Keeping the dialog box open, select the range Q10:Z10 . Here, you want to replace the text Average with Min . In the Replace with text box in the dialog box, overwrite Max with Min, click the Replace All button, and then choose Close . The result is that the average values of the customer census is now calculated in row 8, the maximum values in row 9, and the minimum values in row 10 .

The =AVERAGE(reference) function returns the arithmetic mean of the numbers in reference by adding them together and dividing the total by the count of those numbers . The =MAX(reference) function returns the largest of the numbers in reference . The =MIN(reference) function returns the smallest of the numbers in reference . The =INDEX(array,row_num,column_num) function was explained earlier in a separate section . The =WEEKNUM(serial_number,return_type) function calculates the number of calendar weeks that fall within the serial_number to which the formula refers . return_ type is a value used to define the day on which a week starts . The default setting is 1, and assumes that a week starts on Sunday . If the return_type argument is assigned the number 2, Excel assumes that a week starts on Monday . Incidentally, the WEEKNUM Excel function is of limited use in a European context . This function is essentially used to indicate the week in which January 1 falls each year (i .e ., week 1) . However, this is standardized in Europe, where week 1 is always the first week to include four days that fall in the new year . The WEEKNUM function therefore returns results that don t match the standard European calendar week numbers in some years . In Excel, a date is a serial number . Counting begins (in Microsoft Windows) on January 1, 1900 . What you see as a date in a cell is normally just the specific formatting of a serial number (which can be changed in many different ways) . Excel is only capable of calculating calendar dates (for example, to calculate periods) if it knows that December 24, 2007 is equal to the serial number 39,440, for example) . In the next step, we re going to apply the method used above to create formulas for the branch data . In other words, we re going to calculate the average, maximum, and minimum values for specific rows . Three examples from row 12 are shown in Figure 2-25 .

2

1. Select the range AA12:AC111 and use the method described above to enter a formula in the active cell AA12 to calculate an average based on columns Q to Z . 2. When you ve generated the reference, press F4 repeatedly until the reference as a mixed style (absolute column, relative row), so that our formula now reads =AVERAGE($Q12:$Z12 . 3. Enter a closing parenthesis and then press Ctrl+enter to enter the formula in the entire range selected . 4. Select the generated formula in column AB and replace the text Average with Max as described above . Then replace Average with Min in column AC the same way . Now all that s left to do in this range is enter simple formulas to calculate the difference between the maximum and minimum values for a branch . Let s use a conventional approach for a change: 1. Select cell AD12 and enter the formula =AB12-AC12 . Press enter . 2. Click the fill handle, which is the tiny square in the bottom right corner of the cell border, and replicate the formula by dragging it downwards until you reach cell AD111 . You ve now created a range of formulas for cells whose formatting is identical to the constants provided in the worksheet . To help you keep track of everything, you could assign a different color to all cells containing formulas, as follows: 1. Select the entire worksheet or just a single cell, so that the following command applies to all of the worksheet . 2. Open the Go To dialog box (see Figure 2-20) using the shortcut F5 or Ctrl+g .