EECS 1520 3.0 — Computer Use: Fundamentals

Winter 2016

Announcements

Lecture Schedule

Course Outline

Homework

Grades

Homework

• How to Prepare Your Homework Submission
• Cover Page:    Word Template    Completed Example

Chapters:    0    1    2    3    4    5    6    7    8    9   

Chapter 6 Homework

• Glade Manual, Chapter 6

Support Files

• Exercise 1 - Frequency Counts
• Exercise 3 - Simple Statistical Summaries
• Exercise 4 - Accounts Receivable Data Analysis
• Exercise 5 - Reading a Schedule with VLOOKUP
• Exercise 6 - Using VLOOKUP and MATCH
• Exercise 7 - The INDEX Function

What to Submit

• Cover Page
• Counts worksheet (formula view only) for Exercise 2
• Numbers worksheet (formula view only) for Exercise 2
• Summaries worksheet (formula view only) for Exercise 3.3
• Class worksheet (formula view only) for Exercise 6
• Search worksheet (formula view only) for Exercise 7

Don't forget to put your name and student number in the header, and worksheet tab name in the footer, of each worksheet in the checklist.

Discussion, Hints & Tips

Exercise 7:

      The objective of this exercise is to place formulas in cells B3, B4 and B5 of the Search worksheet so that a SIN entered in cell B2 will produce the other three values found on the same row of the Personal worksheet.  The formula entered in cell B3 must be such that it can be copied to B4 and B5 without further editing. Let Excel use relative cell references to produce the correct result.

      The formula to place in cell B3 of the Search worksheet will have the following structure:

                  =INDEX(array,MATCH(locate row),MATCH(locate column))

where array is practically the entire Personal worksheet (see the already defined named ranges provided in Lab6_Ex7.xls). MATCH(locate row) returns the position of the SIN (cell B2 in the Search worksheet) in column D of the Personal worksheet. This gives the row index into the array. MATCH(locate column) returns the position of the corresponding label in column A of the Search worksheet in the titles found across the first row of the Personal  worksheet. This gives the column index of the desired component.  Column 1 is the Student #, column 2 is the Last Name, and column 3 is the First Name.

      Minor suggestion: From personal experience I've found that sometimes Excel will not properly recognize typed data regardless of how correct it might appear.  So, when creating the Search worksheet, instead of typing in the labels found in column A, copy and paste these labels one by one from the first row of the Personal worksheet. This guarantees that they will match properly.

      Carefully study the online documentation for MATCH.  Every function has online documentation, just follow the link "help on this function" found at the bottom left of the "Insert Function" dialog box encountered when selecting a function. All our uses of MATCH use a match type of 0 (zero). There are two other match types: −1 and +1.  Note what each match type gives you.