Cerritos Home Susan Fuschetto Home Susan Fuschetto Home Cerritos Home Campus Information Admissions Academic Resources Student Resources Administration Personnel Services Search Cerritos College Computer and Information Sciences rectangle Susan Fuschetto header

Susan Fuschetto
(page divider)

CIS 151 Excel For I.T.

Excel Assignment 7 - Data Consolidation, Links, and Formula Auditing

Notes:  This chapter should be easier than chapter 6.  Unfortunately, there are some errors.  Here's a few notes and corrections:

Hands-on exercise 1:  Page 456, step 3 I suggest that you make sure the worksheets are displayed in the same order as shown in figure 7.8.  Also, item e. it states to close all three workbooks, but the Philadelphia and Chicago workbooks should have automatically closed from the previous steps.

Hands-on exercise 2:  Page 469, step 2, items c, e, and g be sure you click the F4 function key, not type the F4 cell reference.  Page 470, figure 7.21 the formula in cell B3 is incomplete.  It should show at the end of the formula the last reference for Chicago [chap7_ho2_chicago.xlsx]Chicago!B3.  Page 471, step 4, the comment you should type is "This is the summary total breakfast sales for the four cities."  Page 472, item l, third bullet should read "Check that cells A1:B1 are still selected and press and hold the CTRL key while you select A2:A15."  Page 473, figure 7.23 shows dates with the year of 2007.  Please use dates that have the current year.

Hands-on exercise 3:  Page 483, step 3, item e. it's B9 not C9.  Item g. it's C9 not C10.  Page 484, step 4, after item f. you need to close the Error Checking dialogue box then do item g.  Item g. follow the steps as directed and then click the Next button, and then click the Edit in Formula Bar button.  After item h. you need to close the Error Checking dialogue box then do item i.  For item i you will need to remove the arrows on both the quarterly and monthly worksheets.  Page 485, step a. it's N24 not N25.  Page 486, step 6 there's an error in the file.  You need to correct this error before doing step 6.  Select cell F3, click the Data tab, Data Validation in the Data Tools group to open up the Data Validation dialogue box.  On the Settings tab change Whole number to Decimal on the Allow option, change F18 to .04 in the Minimum option, and change F3 to .15 in the Maximum option.  Now do the steps in items a. - d.  For item e. use the same validation parameters on the Settings tab (Decimal, .04, and .15) that were used for cell F3.  Item b. the error message is actually "You have entered an invalid commission amount." and not "Value too high or too low!"  Lastly, when testing the validations you will need to either enter decimals such as .25 or type the number with the percent symbol such as 25%.

Practice Exercise 3:  Page 494, item e. here's a hint:  you should setup the validation rules with Decimals for the Allow option, less than or equal to, .08 maximum for cell B6 and .07 for cell B8.

  1. Read Chapter 7 pages 443 - 487.
  2. Do all three of the hands-on exercises described on pages 443 - 487.
  3. Do Practice Exercise #2 SARA Apartment Rentals Corporation on page 492 - 493.
  4. Do Practice Exercise #3 Retirement Calculator on page 494 - 495.
  5. Online students:  Upload the chap7_ho1_summary_solution, chap7_ho2_linking_solution, chap7_ho3_jas_solution, chap7_pe2_apartment_solution, and chap7_pe3_validity_solution files to the Excel 7 assignment on the class website.
  6. Traditional students:  During the lab time show the chap7_ho1_summary_solution, chap7_ho2_linking_solution, chap7_ho3_jas_solution, chap7_pe2_apartment_solution, and chap7_pe3_validity_solution files on your computer screen to me.

Last update: 08/26/08