Last Class Home Work

1) Generate the ASCII chart using the code function.

2) Using the pattern in the book as a template, write a formula which will make a letter uppercase.

3) Write a statement using the AND function and CODE function which tells whether a character is a lowercase character.

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.