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.