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 6 - Data Tables and Amortization Tables

Notes:  This chapter has a great deal of detail and complexity to it.  Please be sure to read everything carefully and take your time.  This chapter will take longer than previous chapters.

Unfortunately there are some errors in your textbook. This sometimes happens when there's a new software release and a new textbook which is the case with your book.  The errors are in the step by step instructions, not on the explanatory pages.

Hands-on Exercise 3, page 404 one of the range names was defined incorrectly.  Before doing item d correct the range name for Medium.  It should be Model.  On the Formulas tab, select Name Manager, select the Medium range name, click the Edit button, change the name to Model, click OK and then Close.  For item i the cell references are incorrect.  C22 should be C20 and C26 should be C22.  Page 405, before doing item i you will need to create a range name for Horsepower.  Select cells G4:G15, then on the Formulas tab, select Name Manager, click the New button, type the name Horsepower, click OK and then Close.   Also, for item i the cell references are incorrect.  H22 should be F20 and H26 should be F22.  And, the formula for F20 is incorrect as well.  The author forgot the " " quotes.  It should be =AVERAGEIFS(Insurance,Model,"Hardtop",Horsepower,">250") .

Hands-on Exercise 3 - Steps 3 - 5 Nested IF logic:  I created a flowchart explaining the nested IF logic for steps 3 through 5.  Nested IFs can be tricky.  Here's the link to the document.  I hope it helps.

Hands-on Exercise 4, page 414 item h format H13 as currency with 2 decimals.  Step 3, page 415 is a little tricky.  Cell D6 is under the house graphic.  Use either the Name Box or CTRL+G goto command to get to cell D6.  Or, click and drag the house graphic out of the way temorarily.  Step 7, page 418, item c you need to create new name ranges.  The steps are select the cells, Formulas Tab, Name Manager, New button, type the name, click OK, then Close.  Repeat the steps for each range name.  Also, for the AmortizationTable range make sure it is A13:H306.  Page 419 when you are done with the steps make sure you have formatted the values as shown in Figure 6.41 and be sure to move the clip art/picture back over cell D6.

Practice Exercise 3, page 430 item d one of the range names is misspelled and has the wrong range.  These errors will cause you trouble later (page 432, b) if you don't fix it.  To correct this error go the Formulas tab, Name Manager, select Endingblanace, click the Edit button, correct the spelling as EndingBalance and the cell to $H$193.

Practice Exercise 4, page 432 item b, huh?  Ignore item b.  Make sure you enter the instructor's name in cell C10 and your name in cell C11.  Be sure to check that your formats match the one's in Figure 6.45.

  1. Read Chapter 6 pages 377 - 419.
  2. Do all four of the hands-on exercises described on pages 377 - 419.
  3. Do Practice Exercises 3 & 4 on pages 430 - 433.
  4. Online students:  Upload the chap6_ho1_textstrings2_solution.xlsx, chap6_ho2_band_solution.xlsx, chap6_ho3_classiccars_solution.xlsx, chap6_ho4_amortization_solution.xlsx, and chap6_pe4_debt_solution.xlsx files Excel 6 assignment on the class website.
  5. Traditional students:  During the lab time show he chap6_ho1_textstrings2_solution.xlsx, chap6_ho2_band_solution.xlsx, chap6_ho3_classiccars_solution.xlsx, chap6_ho4_amortization_solution.xlsx, and chap6_pe4_debt_solution.xlsx files on your computer screen to me.

Last update: 10/20/08