Excel 2011 for the Mac: Advanced Formulas and Functions

Demystifies some of the most challenging of the 300+ formulas and functions in Excel 2011 for the Mac.

Conquer some of the most daunting features in Microsoft Excel once and for all. In this version of his popular course, designed specifically for Excel for Mac users, author Dennis Taylor demystifies some of the most challenging of the 300+ formulas and functions in Excel, and shows how to put them to their best use. Dennis covers key shortcuts, the IF and LOOKUP functions for search and data retrieval, the statistical and math functions, and the date, time, and text functions that make formatting easier. He also covers combining functions to extend their power. These practical examples transition effortlessly to real-world scenarios.

Introduction

• Welcome
• Using the exercise files

1. Formula and Function Tools

• Understanding the hierarchy of operations in Excel formulas
• Using and extending AutoSum button capabilities
• Using absolute and relative references in formulas
• Using mixed references in formulas
• Using the Reference button on the Formulas tab for locating functions
• Using the Formula Builder button for guidance with unfamiliar functions

2. Formula and Function Tips and Shortcuts

• Displaying and highlighting formulas
• Tracking cell dependencies and locating formula sources
• Using entire row and entire column references
• Copying column formulas instantly
• Converting formulas to values with a simple drag
• Updating values without formulas
• Simplifying debugging formulas
• Enhancing readability with range names
• Creating 3D formulas to gather data from multiple sheets

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 and OR 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
• Using VLOOKUP with large tables
• Nesting VLOOKUP 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. Statistical Functions

• Finding the middle value with MEDIAN
• Ranking data without sorting, using the RANK function
• Finding the largest and smallest values with MAX, MIN, LARGE, and SMALL
• Tabulating blank cells with COUNTBLANK
• Using COUNT, COUNTA, and the status bar

6. Power Functions

• Tabulating based on a single criterion with COUNTIF, SUMIF, and AVERAGEIF
• Tabulating based on multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS

7. Selected Financial Functions

• Calculating payments with PMT
• Finding future values with FV
• Determining total amount of future payments with PV

8. Selected Math Functions

• Working with ROUND, ROUNDUP, and ROUNDDOWN
• Working with MROUND, CEILING, and FLOOR for specialized rounding
• Using the INT and TRUNC functions to extract integer data
• Finding the remainder with MOD, and using MOD with conditional formatting
• Using the random-number functions RAND and RANDBETWEEN
• Converting a value between measurement systems with CONVERT
• Using the powerful AGGREGATE function to bypass errors and hidden data
• Using the ROMAN function to display different number systems

9. Date and Time Functions

• Understanding Excel date/time capabilities in formulas
• Using TODAY and NOW functions for dynamic date/time entry
• Identifying the day of the week with WEEKDAY
• Counting working days with NETWORKDAYS
• Determining a completion date with WORKDAY
• Tabulating date differences with DATEDIF
• Calculating end-of-month and future/past dates with EDATE and EOMONTH
• Converting text entries into dates and times with DATEVALUE and TIMEVALUE

10. Text Functions

• Locating and extracting data with FIND, SEARCH, and MID
• Extracting specific data with LEFT and RIGHT
• Removing extra spaces with TRIM
• Using ampersands and CONCATENATE to combine data from different cells
• Adjusting the case within cells using the PROPER, UPPER, and LOWER functions
• Adjusting character content with REPLACE and SUBSTITUTE
• Using other utility text functions: LEN, REPT, VALUE, TEXT

11. 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
• Using array formula techniques with the MATCH function for complex lookups

12. Reference Functions

• Getting data from remote cells with OFFSET
• Returning references with INDIRECT
• Using INDIRECT with data validation for two-tiered pick-list scenarios

13. Information Functions

• Extracting information with the CELL and INFO functions
• Using ISBLANK, ISODD, ISEVEN, ISTEXT, and ISNUMBER
• Using error-checking functions ISERR, ISERROR, IFERROR, and ISNA

Conclusion

• Goodbye