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