Video explanation of part of HW #4: Relational Database Using Offset and Match

Lab 4

Intended topics:

VLOOKUP
HLOOKUP
LOOKUP
MATCH with INDEX

SEARCH to find position within string.
MID to take a slice of a string
LEN to get length of a string
TRIM trims whitespace from beginning and end, and doubled whitespace in the middle.

1) Given a field containing someone's firstname followed by lastname, extract the first name.
2) Given the same field, extract the last name.

UDFs.

Homework:
1) Do relational database with MATCH and INDEX.
2) Given a field containing someone's firstname middlename lastname, extract the middle name.
3) Let us say that a sentence can contain up to 5 words. Combining IF with SEARCH, write a formula which will tell you how many words exist.
4) Write a UDF which returns X squared.

Lab 3

Intended topics:

Functions:
MATCH
OFFSET

PROPER

Array Formulas Introduction
Review Homework in book, with and without array formulas


Homework:
Actually do those three
And Modify earlier example using match and offset to allow a primary key, rather than just relying on Order.

CS88 - HW2 Assignment: Double Indirect to Implement "Relational Database" in Excel

Sorry for the roughness of the audio. I'll try to fix in the future. This describes what we did in class, and how I would like you to extend it for homework.

Lab Two

Intended topics:

VLOOKUP
INDIRECT
UPPER
LOWER
MID
LEN
IF

If name starts with J, then get 100 in the class. Otherwise, do a VLookUp

If grade is less than 60, give an F, otherwise give a B.

If second letter is an A, then make the entire name uppercase. Otherwise, make it lowercase.

Formula auditing tools

Homework:
In the online book, page 289, questions 23 - 27.

A Walkthrough of HW 1 Exercise 41

Welcome

Welcome to CS 88, Advanced Productivity Tools for Business.

You can see some of the material from last semester below.

For now, I will provide a link to the book on Amazon.com. The book is Excel 2003 Formulas, by John Walkenbach. Check back later for more information.

Homework is to get the book and to skim through the first two chapters. Also, based on the work we did in the lab, make similar "forms" in Excel, with similar formatting, with a tan background for the form background and a turquoise background for the title background. In this other book, available here, on page 104, perform exercises 41 through 44, as well as 49. See if you can do exercise #50. Throughout, gives names to the input cells. Hint for #50: The most straightforward way to approach this is with a text formula, from chapter 5 in the book. Sign the Excel spreadsheet.

Submit it through Blackboard or, barring that, email it to me.

Due date: Before next class.



More to come later.