Microsoft Excel 2003 Formulas and Functions Class 3
| Duration: | 1 day (about 6 to 7 hours) |
| Prerequisite: | Microsoft Office Excel 2003 Formulas and Functions Class 1 |
| Course Materials: | Course manual with CD containing exercises |
| Prices: | $225 for 1 student (one-on-one training) $160 each for 2 to 3 students $130 each for 4 to 6 students $130 for the first 6 students, $100 for each additional student |
| Description: | This is the third day of our Excel Formulas and Functions course. The VLOOKUP function is an important function that we thoroughly explore. This function also serves as a good example of how to understand and approach other functions. We then learn about more complex lookup functions. We then learn a few advanced financial formulas and build financial schedules. We finished by creating formulas that will challenge what you learned in class. We also learn more about text, date, and logical functions on an Intermediate level. Finally, we learn about Array formulas. This is the second day of a 3-day Excel Formulas and Functions class. You can choose to take all three days of training or to cherry-pick topics for a custom class. |
The Anatomy of the VLOOKUP Function
- Understanding the VLOOKUP Function Description
- Creating a Basic VLOOKUP Function
- Creating a Drop-Down Menu for Your Values
- Looking Up an Exact Value
- Using VLOOKUP to Enter Values in Other Cells
- Looking Up an Approximate Value
- Concatenating Lookup Values
Specialized Lookup Formulas
- Looking Up a Value to the Left
- Performing a Case-Sensitive Lookup
- Choosing among Multiple Lookup Tables
- Performing a Two-Way Lookup
- Performing a Two-Column Lookup
Working with Advanced Financial Formulas
- Calculating the Interest and Principle Components
- Using the IPMT and PPMT Functions
- Using the CUMIPMT and CUMPRINC Functions
- Using the NPV Function
- Using the NPV Function to Calculate Accumulated Amounts
- Using the IRR Function
Financial Schedules
- Creating Amortization Schedules
- Summarizing Loan Options Using a Data Table
Tools and Methods for Debugging Formulas
- What Exactly is Formula Debugging?
- Formula Problems and Solutions
- Excel's Auditing Tools
Formula Challenges
- Class Project