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.

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.

How to use SQL from Excel to Access

Sub Button1_Click()
Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\jwaxman\My Documents\db1.mdb;" & _
"User Id=;" & _
"Password="
cn.Open

Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
cmd.ActiveConnection = cn
cmd.CommandText = "Select * from Cust"
Set rs = cmd.Execute

'$#,##0_);[Red]($#,##0);[blue]General

Dim I As Integer
I = 1
While Not rs.EOF
Range("A" & I).Value = rs("Name") & " " & rs("Age")
rs.MoveNext
I = I + 1
Wend
End Sub


Homework with a scan will be up shortly

Excel Take-home Exam #2

Available here:
http://docs.google.com/Doc?id=ajbqhgmq9qdz_80cqnrngds

HW:
Write an Absolute formula.

Takehome test #1

Available here:

Homework

The Choose function in Excel uses its first argument to look up into a list formed by the rest of the arguments. Thus, Choose(1, 6, 7) will give 6, while Choose(2, 6, 7) will give 7. You can use Choose with the first argument being the month number (1 through 12) and the next twelve arguments being the number of days in the month. Remember, 30 days has September, April, June, and November. All the rest have 31. Except for February, which in a leap year has 29, but otherwise has 28. Don't worry about leap years. Pretend that this year is not a leap year.

Thus, you can write a formula which will look up a month and give you the number of days in that month.

Next, instead of specifying manually what the month number is, use the Excel MONTH function to extract the month number from some date.

Now, know that if you add the number of days in the month to a date within that month, you will arrive at the same day of the month, but in the next month.

So, put a date into A1. In A2, write a formula using the above information, which will be the same day of the month, but the next month. 1/6/08 will become 2/6/08. And 7/7/08 will become 8/7/08. This is different than just adding the number 30 to the previous date.

The Book For The Class

The book is Excel 2003 Formulas, by John Walkenbach.

Regular expressions in Microsoft Word, Excel

Here are some interesting articles on wildcards and regular expression matching. We might cover some of this. Don't worry if you can't understand this just yet. We will discuss it in class, at some point. For now, just some interesting reading.

1. Add Power to Word Searches with regular expressions
• this is somewhat powerful, but it is fairly easy to encounter limitations to its power.
2. Putting Regular Expressions to Work in Word
• A follow up article. We might try going through these examples.
3. Regular Expressions in Excel
   

HW

Go to this article/web site and do exercises 1 and 2.

Video Review of HW $ 4

Some clarification about the homework.
In this Excel homework, we record our first Excel macro. We also use CHAR and CODE to make a character uppercase, and use IF with those two built-in-functions to make the lowercase work even for characters which are already lowercase. We explain what was intended by the portion of the assignment about entering in REPT formulas.

Video Review of HW#3

Part 1:
In which we use the MID function in Excel to extract certain substrings, and then concatenate them together using the ampersand operator.


Part 2:
We play a bit with the CODE and CHAR function, and the LOWER function for good measure. All this to get a firmer sense of how characters, the building blocks of strings, work.

HW #5

Write A UDF

1. Take in X and Y, evaluate to X times Y

2. Takes in X, evaluates to yes if negative, no if >=0

3. Also, Write a formula using IF and And to evaluate to yes or no

depending on whether or not then # is between 20 and 80

4. Do the same using OR function and IF function

HW # 4:

Read Chapter 22, record the macro as specified on the second to last page of the chapter.

pg 116
1. Convert lowercase to uppercase
2. Convert uppercase and lowercase to lowercase
pg 121
3. type in REPT formulas

No Class Today

No class today -- Lincoln's birthday

Video Review of Homework #2

Here is a link to homework #2, which was practice in using Sheet references and of the correspondence between A1 and R1C1 reference style, in Excel.

This video is a walkthrough:

HW #3

1. Put the text Josh Horatio Waxman into cell A1. Use the MID function to extract the first letters of the first name, middle name and last name. Put them together again to form J. H. W.

2. In column A, put the letters A through Z, a through z, and the numbers 0 through 9. In column B, use the CODE function to find the corresponding ASCII code. In column C, use the CHAR function to covert the ASCII codes in column B back into symbols.

If You Need Tutoring For This Class

It seems that the Computer Science Department offers free tutoring. Stop by the CS Departmental office and ask them about it, in terms of what the hours are.

Video Review of Homework #1

Here is a link to homework #1, which is a gradebook. The video below shows a walk-through of this homework:

HW #2

Note: A partial solution to the previous assignment, which we worked on in the lab, is now up on Blackboard, within "Course Documents."

1. Put values in cell A1 within Sheet1 and Sheet2. In cell A1 on Sheet3, find the sum of the A1 cells of Sheets 1 and 2.

2. Type in the following formulas in one Excel referencing convention and change to the other convention. Type them in while in cel A1.

R4c[-2]
c32 (while in z9 and in A1)
C$92
R5C5

Using the Digital Dropbox To Submit Homeworks

We'll go over this in class, so don't worry about it, but here is a video taking you through the steps.

HW #1

Make a grade book. Make up the names of 10 students (first name and last name in two separate columns), and give them grades for a midterm and a final.
The grade for the course should be 40% of the midterm and 60% of the final.
Use VLOOKUP to match the course grade with a letter grade.
Use string concatenation to concatenate the first name with the last name.
Calculate the minimum grade, the maximum grade, the mean, the median, the mode, and the range. Use this website's definition of these terms.
Give the third student in your list a 70. Use Goal Seek to find out the exact grade he needs on the final to get a semester average of 75.

Welcome to the new semester!

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.