Lynda Excel 2010 Advanced Formulas and Functions

Author: Dennis Taylor
Duration (hr:min): 05:05   
Released on: 2010
Exercise files: Yes
Software works on: Windows only

In Excel 2010: Advanced Formulas and Functions, author Dennis Taylor demystifies formulas and some of the most challenging of the nearly 400 functions in Excel and shows how to put them to their best use. The course reviews the building-block functions, along with a few critical keyboard shortcuts that will speed up working with Excel data. It also covers how to perform advanced searching and data retrieval with LOOKUP functions, tabulate data with counting, statistical, and math functions, reformat data with text functions, and work with financial data using advanced formulas. Exercise files accompany the course.

Topics include:

• Referencing, copying, updating, and converting formulas
• Using the logical functions and creating compound logic tests
• Searching for and matching data based on specific criteria
• Reconfiguring cell data using text functions
• Calculating dates, times, and days of the week
• Analyzing mathematical and financial data
• Using the power functions, COUNTIFS, SUMIFS, and AVERAGEIFS
• Working with rounding functions
• Returning cell references

Content:

Introduction - 00:01:49

• Welcome - 00:01:25
• Using the exercise files - 00:00:24

1. Formula and Function Tips and Shortcuts - 00:47:12

• Using the entire row/column references - 00:05:43
• Copying column formulas instantly - 00:04:35
• Converting formulas to values with a simple drag - 00:05:10
• Creating 3D formulas to gather data from multiple sheets - 00:05:17
• Updating values without formulas - 00:04:25
• Displaying and highlighting formulas - 00:07:15
• Simplifying debugging formulas - 00:06:29
• Enhancing readability with range names - 00:08:18

2. Formula and Function Tools - 00:22:32

• Reviewing function basics - 00:07:12
• Using and extending AutoSum - 00:04:55
• Using absolute and relative references - 00:04:52
• Using mixed references - 00:05:33

3. IF and Related Functions - 00:16:39

• Exploring IF logical tests and using relational operators - 00:04:43
• Creating and expanding the use of nested IF statements - 00:04:47
• Using the AND, OR, and NOT functions with IF to create compound logical tests - 00:07:09

4. Lookup and Reference Functions - 00:41:01

• Looking up information with VLOOKUP and HLOOKUP - 00:04:27
• Finding approximate matches with VLOOKUP - 00:06:22
• Finding exact matches with VLOOKUP - 00:04:21
• Nesting LOOKUP functions - 00:05:51
• Finding table-like information within a function with CHOOSE - 00:03:51
• Locating data with MATCH - 00:05:03
• Retrieving information by location with INDEX - 00:04:51
• Using MATCH and INDEX together - 00:06:15

5. Power Functions - 00:12:27

• Tabulating information on a single criterion with COUNTIF, SUMIF, and AVERAGEIF - 00:04:49
• Tabulating information on multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS - 00:07:38

6. Statistical Functions - 00:11:35

• Finding the middle value with MEDIAN - 00:01:35
• Ranking data without sorting with RANK - 00:04:50
• Finding the magnitude data with LARGE and SMALL - 00:01:46
• Tabulating blank cells with COUNTBLANK - 00:03:24

7. Data Functions - 00:23:27

• Understanding Excel date/time capabilities in formulas - 00:07:18
• Identifying the day of the week with WEEKDAY - 00:06:02
• Counting working days with NETWORKDAYS - 00:03:05
• Determining a completion date with WORKDAY - 00:02:24
• Tabulating date/time differences with DATEDIF - 00:04:38

8. Math Functions - 00:25:36

• Working with rounding functions - 00:08:03
• Finding the remainder with MOD and using MOD with conditional formatting - 00:04:42
• Building random number generators with RAND and RANDBETWEEN - 00:04:42
• Converting a value between measurement systems with CONVERT - 00:03:07
• Using the powerful new AGGREGATE function to bypass errors and hidden data - 00:05:02

9. Array Formulas and Functions - 00:27:16

• Extending formula capabilities with arrays - 00:07:12
• Counting unique entries in a range with an array formula - 00:04:46
• Determining frequency distributions with FREQUENCY - 00:03:06
• Flipping row/column orientation with TRANSPOSE - 00:04:38
• Building analysis via regression techniques with TREND and GROWTH - 00:03:07
• Combining multiple functions in arrays - 00:04:27

10. Text Functions - 00:36:11

• Locating and extracting data with FIND and MID - 00:06:18
• Extracting specific data with LEFT and RIGHT - 00:03:02
• Removing excess spaces with TRIM - 00:05:42
• Using CONCATENATE with functions - 00:04:24
• Adjusting case within cells with PROPER, UPPER, and LOWER - 00:04:17
• Adjusting character content with REPLACE and SUBSTITUTE - 00:05:56
• Reviewing additional text functions - 00:06:32

11. Financial Functions - 00:13:30

• Calculating payments with PMT - 00:04:34
• Finding future values with FV - 00:04:03
• Determining total amount of future payments with PV - 00:04:53

12. Information Functions - 00:11:13

• Working with the IS information functions - 00:04:42
• Using error-checking functions: ISERR, ISERROR, IFERROR - 00:06:31

13. Reference Functions - 00:14:29

• Getting data from remote cells with OFFSET - 00:04:46
• Returning references with INDIRECT - 00:09:43

Conclusion - 00:00:41

• Final thoughts - 00:00:41