Lynda.com Excel 2010 Advanced Formulas and Functions (reupload)

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. Course outline on next page
Introduction

Welcome

Using the exercise files

1. Formula and Function Tips and Shortcuts
Using the entire row/column references
Copying column formulas instantly
Converting formulas to values with a simple drag
Creating 3D formulas to gather data from multiple sheets
Updating values without formulas
Displaying and highlighting formulas
Simplifying debugging formulas
Enhancing readability with range names

2. Formula and Function Tools
Reviewing function basics
Using and extending AutoSum
Using absolute and relative references
Using mixed references

3. IF and Related Functions
Exploring IF logical tests and using relational operators
Creating and expanding the use of nested IF statements
Using the AND, OR, and NOT functions with IF to create compound logical tests



4. Lookup and Reference Functions
Looking up information with VLOOKUP and HLOOKUP
Finding approximate matches with VLOOKUP
Finding exact matches with VLOOKUP
Nesting LOOKUP functions
Finding table-like information within a function with CHOOSE
Locating data with MATCH
Retrieving information by location with INDEX
Using MATCH and INDEX together

5. Power Functions
Tabulating information on a single criterion with COUNTIF, SUMIF, and AVERAGEIF
Tabulating information on multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS

6. Statistical Functions
Finding the middle value with MEDIAN
Ranking data without sorting with RANK
Finding the magnitude data with LARGE and SMALL
Tabulating blank cells with COUNTBLANK

7. Data Functions
Understanding Excel date/time capabilities in formulas
Identifying the day of the week with WEEKDAY
Counting working days with NETWORKDAYS
Determining a completion date with WORKDAY
Tabulating date/time differences with DATEDIF

8. Math Functions
Working with rounding functions
Finding the remainder with MOD and using MOD with conditional formatting
Building random number generators with RAND and RANDBETWEEN
Converting a value between measurement systems with CONVERT
Using the powerful new AGGREGATE function to bypass errors and hidden data

9. Array Formulas and Functions
Extending formula capabilities with arrays
Counting unique entries in a range with an array formula
Determining frequency distributions with FREQUENCY
Flipping row/column orientation with TRANSPOSE
Building analysis via regression techniques with TREND and GROWTH
Combining multiple functions in arrays

10. Text Functions
Locating and extracting data with FIND and MID
Extracting specific data with LEFT and RIGHT
Removing excess spaces with TRIM
Using CONCATENATE with functions
Adjusting case within cells with PROPER, UPPER, and LOWER
Adjusting character content with REPLACE and SUBSTITUTE
Reviewing additional text functions

11. Financial Functions
Calculating payments with PMT
Finding future values with FV
Determining total amount of future payments with PV

12. Information Functions
Working with the IS information functions
Using error-checking functions: ISERR, ISERROR, IFERROR

13. Reference Functions
Getting data from remote cells with OFFSET
Returning references with INDIRECT

Conclusion

Final thoughts