Contents
- Example 1: String functions - LEFT, MID, RIGHT, CONCATENATE / &, TRIM, SUBSTITUTE, FIND, LEN
- Example 2: AND, OR, IF
- Example 3: Handling errors using IFERROR and ISERROR
- Example 4: Table look up - MATCH and INDEX (or OFFSET)
- Example 5: Referring to different tables - INDIRECT
- Example 6: Multiple criteria SUMIF/COUNTIF and SUMIFS/COUNTIFS/AVERAGEIFS
- Example 7: Dynamic ranges - OFFSET revisited
- Example 8: Calculating while filtering rows / calculating with visible cells only - multifunctional SUBTOTAL
- Example 9: Dynamic sorting or finding nth largest or nth smallest value - LARGE and SMALL. Office365/Excel 2021: SORT.
- Example 10: Avoiding additional columns - SUMPRODUCT
- Array formula intermezzo *
- Example 11-A: Connecting a second table with multiple columns to a first table**: MATCH versus VLOOKUP (and HLOOKUP)
- Example 11-B: fast VLOOKUP
- Example 11-C: XLOOKUP
- Example 12: Creating a histogram using COUNTIF ***
- Example 13: Transposing data - TRANSPOSE versus OFFSET in combination with COLUMN
- Example 14: Random number generation - RAND() and several distribution types → useful for Monte Carlo simulation
* File includes array formula examples. However, try to avoid using array formulas.
** Nowadays this can be done by 'underlying' Excel PowerPivot.
*** MS Excel 2016 offers the histogram as a new standard chart.
Sneak preview

