Lynda.com.Excel 2007: Advanced Formulas and Functions

Introduction
Welcome 01:30 10.9 MB
Using the exercise files 01:07 1.3 MB
1. Formula and Function Tips/Shortcuts
Using the entire row/column references 04:01 5.2 MB
Copying column formulas instantly 04:56 5.7 MB
Converting formulas to values with a simple drag 04:39 5.0 MB
Creating 3D formulas to gather data from multiple sheets 06:06 6.5 MB
Updating values without formulas 03:34 3.8 MB
Showing all formulas with a single command 04:52 7.6 MB
Highlighting formulas with two clicks 04:25 5.1 MB
Simplifying debugging formulas 05:36 6.2 MB
Creating range names to enhance readability 06:20 7.6 MB
2. Excel Functions
Understanding and using basic Excel functions 09:43 11.0 MB
Using common functions: SUM, AVERAGE, MAX, and MIN 05:58 7.1 MB
Using and extending AutoSum 05:19 6.1 MB
3. IF and Related Functions
Exploring IF logical tests and using relational operators 05:46 5.9 MB
Creating and expanding the use of nested IFs 05:43 5.6 MB
Using the AND, OR, and NOT functions with IF to create compound logical tests 06:54 7.1 MB
4. Lookup and Reference Functions
Looking up information with VLOOKUP and HLOOKUP 05:56 7.0 MB
Using VLOOKUP for approximate matches 07:38 9.4 MB
Using VLOOKUP for exact matches 04:59 5.2 MB
Nesting Lookup functions 03:28 4.1 MB
Finding table-like information within a function with CHOOSE 04:02 4.0 MB
Locating data with MATCH 05:35 6.6 MB
Retrieving information by location with INDEX 02:33 3.0 MB
Using MATCH and INDEX together 06:37 8.4 MB
5. Power Functions
Tabulating information on a single criterion with COUNTIF, SUMIF, and AVERAGEIF 06:14 6.8 MB
Tabulating information on multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS 06:18 7.2 MB
6. Statistical Functions
Finding the middle value with MEDIAN 02:54 3.6 MB
Ranking data without sorting with RANK 04:51 6.1 MB
Finding the magnitude data with LARGE and SMALL 02:11 2.4 MB
Tabulating blank cells with COUNTBLANK 03:16 4.8 MB
7. Date Functions
Understanding Excel date/time capabilities in formulas 10:36 11.3 MB
Identifying the day of the week with WEEKDAY 04:16 4.8 MB
Counting working days with NETWORKDAYS 03:12 3.4 MB
Determining a completion date with WORKDAY 04:51 5.1 MB
Tabulating date/time differences with DATEDIF 02:21 2.5 MB
8. Math Functions
Working with rounding functions 11:30 12.8 MB
Finding the remainder with MOD 04:19 4.6 MB
Building random number generators with RAND and RANDBETWEEN 05:52 7.9 MB
Converting a value between measurement systems with CONVERT 03:59 4.0 MB
9. Array Formulas and Functions
Extending formula capabilities with Arrays 07:40 10.0 MB
Counting unique entries in a range with an Array formula 04:44 6.5 MB
Determining frequency distributions with FREQUENCY 03:39 4.5 MB
Flipping row/column orientation with TRANSPOSE 03:28 4.1 MB
Building analysis via regression techniques with TREND and GROWTH 02:38 2.8 MB
Combining multiple functions in arrays 05:39 6.4 MB
10. Text Functions
Locating and extracting data with FIND and MID 07:49 8.4 MB
Extracting specific data with LEFT and RIGHT 03:12 3.5 MB
Removing excess spaces with TRIM 04:33 5.0 MB
Using CONCATENATE with functions 04:59 5.2 MB
Adjusting case within cells with PROPER, UPPER, and LOWER 03:48 4.7 MB
Adjusting character content with REPLACE and SUBSTITUTE 05:51 6.4 MB
Reviewing additional text functions 08:52 10.1 MB
11. Financial Functions
Calculating payments with PMT 06:52 8.4 MB
Finding future values with FV 05:25 5.8 MB
Determining total amount of future payments with PV 02:46 3.0 MB
12. Information Functions
Working with the IS information functions 05:40 5.9 MB
Using error-checking functions: ISERR, ISERROR, IFERROR 08:14 9.3 MB
Getting data from remote cells with OFFSET 05:50 6.1 MB
Returning references with INDIRECT 10:54 12.7 MB
Conclusion
Goodbye