Day/Time: | Tuesday, 1:00-4:00 |
Credits: | 3 |
Prerequisites | BUSN 206, or placement on Technology Assessment exam, or permission of instructor |
Instructor: | Dr. Channah F. Naiman |
email: | naiman@htc.edu Please be sure to include the course# in the subject line!! |
Office hours: | By appointment, generally before or after class. |
Texts: | Excel
for marketing
Managers, by Ivana Taylor and Bill Jelen. ISBN: 9781932802139 Publisher: Holy Macro, 2006 VBA for Modelers, by S. Christian Albright. ISBN: 9781133190875 Publisher: Southwestern Cengage Learning |
If you think that you will have to miss class for work, for travel or to get engaged or married, I strongly suggest that you take this course another semester. I will NOT excuse you from any work for any reason, and you will not earn full credit for attendance if you miss class. The most important reason for this is that much of the work cannot be made up outside of class. We will be experimenting with data, taking short detours with various data, and so forth. The learning is in the process. You can't simply fill out a spreadsheet, turn it in, and consider that you have "made up the work". In the event of an emergency or illness, we can discuss whether a late drop can be arranged.
Attendance/Participation | 10% |
Lab/In-class/Homework Assignments/Quizzes | 30% |
Topic Presentation(s) | 10% |
Project | 20% |
Exams | 30% |
Date | Topic | Text/Materials | Assignments/Files | ||
---|---|---|---|---|---|
2/05 | Introduction to Course Syllabus Review What Excel can do:
Prerequisite knowledge: |
MM, selected pages (listed below per topic) Mr. Excel.com The Smart Method youtube |
In-class assignment: ---Baseline Project: saving for something special Baseline Project Solution (if you are stuck) |
||
---Headings and footers | pp. 10-11 | ||||
---copying worksheets | p. 12 | ||||
---customized formats | pp. 14-16 | ||||
---quicksum | pp. 17-24 | file: File-8-quicksum-2010 | |||
---subtotals | |||||
---special autofills | p. 27 | ||||
---see formulas (w and w/o goto) | pp. 40-41 | ||||
---arrays (CSE formulas) | p. 44 | file: File-37-CSE | |||
---advanced filters | pp. 56-62 | file: 23-Autofilter-2010 | |||
---conditional formatting | pp. 66-68 | file:
16-conditional-formatting-2010 video: formula-driven conditional formatting file: SalesSummaryFirstQuarter2008 |
|||
2/12 | ---VLOOKUP/HLOOKUP | pp. 80-86 | file:
looking-up-data-2010 file: Cust-Name-and-rep-number-no-repname-2010-st file: rep-number-and-name-2010 file: rep-number-no-repname-no-CustName-2010-st |
||
---Text() and Value() | |||||
Fixing bad data | p. 86 | file: 34-Bad-Data-2010 | |||
Match Function | p. 87 | file: LookingUpData-2010 sheet: IndexOriginal |
|||
Index/Match | we will revisit index/match several more times | ||||
2/19 |
|
DUE:
First pass at String
Assignment file: Cleaning Text file: compare-2-lists file: instructions for comparing 2 lists |
|||
2/26 | continuing Strings | ||||
3/05 |
|
class notes | file: compare-2-lists file: instructions for comparing 2 lists |
||
3/12 | Logic, continued cond. format, cont'd |
free tutorial for conditional
formatting microsoft's tutorial Create Student Data raw data |
|||
Custom Sorts | p. 90 | file: custom sorts | |||
Dates: formatting, arithmetic | pp. 93-96 | file: 4-Dates-2010 | |||
joining text | pp. 96-99 | file: 28-CalculatingText-2010 sheet: JoinOriginal |
|||
Transpose | pp. 99-100 | file: transpose-2010 | |||
counting records | pp.101-102 | file: 24-CountIf-2010 | |||
combine Sum and If --array formulas revisited |
pp. 102-103 | ||||
Text functions revisited | pp. 103-104 | file: 26-Textfunctions-2010 | |||
4/09 | Data Cleaning | In-class assignment: Student Data | |||
4/16 | catch-up, review | ||||
4/23 | Midterm Exam (hands on) | ||||
4/30 | What is VBA? | Albright, Ch.1 | |||
Excel Object Model | Albright, Ch. 2 | ||||
Object-oriented programming | |||||
VBE | Albright, Ch. 3 | file: first program file: first program finished |
|||
5/07 | Recording macros | Albright, Ch. 4 | file: recording macros file: recording macros finished |
||
playing with macros | various short assignments | ||||
5/14 | Getting Started VBA | Albright, Ch. 5 | |||
(Intro Programming) | |||||
Exercises/Assignment | |||||
5/21 | Control Structures | Albright, Ch. 7 | |||
5/28 | Forms | Albright, Ch. 11 | |||
6/04 | |||||
6/11 |