A list of homeworks

1) 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.

2) In the online book, page 289, questions 23 - 27.

3) Implement the database as described in the video here:

4) Modify earlier example using match and offset to allow a primary key, rather than just relying on Order.

5) Homework:
a) Do relational database with MATCH and INDEX.
b) Given a field containing someone's firstname middlename lastname, extract the middle name.
c) 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.
d) Write a UDF which returns X squared.

6)
a) Generate the ASCII chart using the code function.
b) Using the pattern in the book as a template, write a formula which will make a letter uppercase.
c) Write a statement using the AND function and CODE function which tells whether a character is a lowercase character.

7)
Ch 17, do the Progress towards a goal, and the Gantt chart.

Final Exam

Write the answers in your booklets.
http://docs.google.com/Doc?id=ajbqhgmq9qdz_111gsk2rhnn

Time and Place for the Final

B131 (the regular lab)
time: 6:15-8:15 PM, today

Dec 22
6:15 - 8:15
Room TBA, but we should have a lab

Full takehome is now up

Reload the page, to check it out.

Link to Midterm #2

http://docs.google.com/Doc?id=ajbqhgmq9qdz_128c4d3tvpk

work in progress, first half

Link to Prentice Hall to get downloads

http://prenhall.com/grauer/

Connecting to an Access Database from Excel Using VBA

Sub GetMyData()
Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\josh\Investment.mdb;User id=admin;"
cn.Open

Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = cn
'cmd.CommandText = "Delete * From Clients Where FirstName='" & Range("E2") & "'"
cmd.CommandText = "Update Clients Set FirstName='John' Where SSN='111111111'"

cmd.Execute
Exit Sub

Dim rs As ADODB.Recordset
Set rs = cmd.Execute
Dim I As Integer
I = 1
While Not rs.EOF
Range("A" & I).FormulaR1C1 = rs("FirstName")
Range("B" & I).FormulaR1C1 = rs("LastName")
I = I + 1
rs.MoveNext
Wend
End Sub

Circular References In Excel, Pt 2

4) Circular References In Excel, part 4


5) Circular References In Excel, part 5


6) Circular References In Excel, part 6


7) Circular References In Excel, part 7

Circular References In Excel, Pt 1

Take-home Test to be posted later...

1: Circular References in Excel part 1:


1: Circular References in Excel part 2:


3: Circular References in Excel part 3:

A Homework

Ch 17, do the Progress towards a goal, and the Gantt chart.

Take home midterm #1

The midterm is available here.

Take home midterm

is still in development. Sorry.
:(
Check back later.

Schedule for coming weeks

Wednesday, Oct 8 -- no class
Monday, Oct 13 -- Columbus Day, no class
Tuesday, Oct 14 -- Monday schedule, but even so, no class
Wednesday, Oct 15 - no class
Monday, Oct 20 -- no class
Wednesday, Oct 22 -- no lab, but there will be lecture. (at 7:30 PM-8:20 PM)
Monday, Oct 27 -- there will be class

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.