Using the powerful AGGREGATE function to bypass errors and hidden data

Contents

• Introduction Introduction

  • Welcome

    50s
  • Using the exercise files

    21s
• 1. Formula and Function Tips and Shortcuts 1. Formula and Function Tips and Shortcuts

  • Displaying and highlighting formulas

    4m 43s
  • Using Auditing tools

    6m 15s
  • Using entire row and column references

    4m 4s
  • Copying column formulas instantly

    2m 45s
  • Converting formulas to values with a drag

    4m 59s
  • Updating values without formulas

    3m 26s
  • Debugging formulas with the F9 key

    4m 2s
  • Enhancing readability with range names

    5m 23s
  • Tabulating data from multiple sheets

    8m 20s
• 2. Formula and Function Tools 2. Formula and Function Tools

  • Hierarchy of operations in formulas

    6m 28s
  • Formulas tab for locating functions

    5m 34s
  • Insert Function for learning about unfamiliar functions

    5m 20s
  • Extending the capabilities of AutoSum

    6m 10s
  • Absolute and relative references

    5m 4s
  • Using mixed references in formulas

    6m
  • Using autocalculate in the status bar

    5m 45s
• 3. IF and Related Functions 3. IF and Related Functions

  • IF logical tests

    4m 27s
  • Expanding nested IF statements

    4m 30s
  • AND, OR, and NOT functions with IF

    9m 2s
  • Using IFS for multiple conditions

    3m 27s
• 4. Lookup and Reference Functions 4. Lookup and Reference Functions

  • Looking up information with VLOOKUP and HLOOKUP

    4m 30s
  • Finding approximate matches with VLOOKUP and HLOOKUP

    7m 16s
  • Finding exact matches with VLOOKUP

    5m 30s
  • Nesting lookup functions

    4m 12s
  • Using VLOOKUP with large tables

    4m 21s
  • Finding table-like information within a function using the CHOOSE function

    2m 44s
  • Using the SWITCH function for formula-embedded selection

    5m 40s
  • Locating data with the MATCH function

    3m 43s
  • Retrieving information by location

    2m 53s
  • Using MATCH and INDEX functions together

    4m 14s
• 5. Power Functions 5. Power Functions

  • Tabulating data using a single criterion

    4m 33s
  • Tabulating data using multiple criteria

    5m 19s
  • Using MAXIFS and MINIFS

    4m 14s
  • Preventing double counting

    4m 48s
• 6. Statistical Functions 6. Statistical Functions

  • Finding the middle value with MEDIAN and most common value with MODE

    2m 21s
  • Ranking data without sorting with RANK and RANK.EQ

    4m 8s
  • Finding the largest and smallest values with the LARGE and SMALL functions

    1m 47s
  • Tabulating blank cells with the COUNTBLANK function

    5m 29s
  • Using COUNT, COUNTA, and the status bar

    3m 32s
• 7. Math Functions 7. Math Functions

  • Working with the ROUND, ROUNDUP, and ROUNDDOWN functions

    6m 13s
  • Working with MROUND, CEILING, and FLOOR for specialized rounding

    3m 20s
  • Using the INT and TRUNC functions to extract integer data

    3m 58s
  • Finding the remainder with MOD and using MOD with conditional formatting

    5m 26s
  • Exploring practical uses for the RAND and RANDBETWEEN functions

    4m 57s
  • Converting a value between measurement systems with CONVERT

    2m 55s
  • Using the powerful AGGREGATE function to bypass errors and hidden data

    6m 32s
  • Using the ROMAN and ARABIC functions to display different number systems

    3m 25s
• 8. Date and Time Functions 8. Date and Time Functions

  • Understanding Excel date and time capabilities in formulas

    9m 52s
  • Using the DATE, YEAR, MONTH, DAY, TIME, HOUR, MINUTE, and SECOND functions

    4m 4s
  • Using the TODAY and NOW functions for dynamic date and time entry

    6m 49s
  • Identifying the day of the week with the WEEKDAY function

    4m 40s
  • Counting working days with the NETWORKDAYS function

    5m 26s
  • Determining a completion date with the WORKDAY function

    4m 10s
  • Tabulating date differences with the DATEDIF function

    6m 15s
  • Calculating end-of-month and future and past dates with EDATE and EOMONTH

    4m 58s
• 9. Array Formulas and Functions 9. Array Formulas and Functions

  • Extending formula capabilities with array formulas

    5m 7s
  • Counting unique entries in a range with an array formula

    5m 51s
  • Determining frequency distributions with the FREQUENCY function

    3m 53s
  • Flipping row and column orientation with TRANSPOSE

    5m 10s
  • Building analysis via regression techniques with TREND and GROWTH

    2m 59s
  • Using array formulas and the MATCH function for complex lookups

    5m 30s
• 10. Reference Functions 10. Reference Functions

  • Getting data from remote cells with the OFFSET function

    5m 36s
  • Returning references with the INDIRECT function

    3m 11s
  • Using INDIRECT with Data Validation for two-tiered pick list scenarios

    7m 44s
• 11. Text Functions 11. Text Functions

  • Locating and extracting data with the FIND, SEARCH, and MID functions

    6m 20s
  • Extracting specific data with the LEFT and RIGHT functions

    3m 22s
  • Using the TRIM function to remove unwanted spaces in a cell

    5m 43s
  • Using ampersands and CONCATENATE to combine data from different cells

    5m 51s
  • Using the CONCAT and TEXTJOIN to combine data from different cells

    6m 8s
  • Adjusting alphabetic case with the UPPER, LOWER, and PROPER functions

    3m 48s
  • Adjusting character content with the REPLACE and SUBSTITUTE functions

    5m 41s
  • Using the utility text functions: TEXT, REPT, and LEN

    5m 7s
• 12. Information Functions 12. Information Functions

  • Extracting information with the CELL and INFO functions

    4m 10s
  • Using ISBLANK, ISODD, ISEVEN, ISTEXT, ISNONTEXT, and ISNUMBER

    5m 9s
  • Using the ISERR, ISERROR, IFERROR, and ISNA error-checking functions

    7m 53s
  • Tracking and highlighting formula cells with the ISFORMULA function

    3m 2s
• Conclusion Conclusion

  • Next steps

    33s