﻿ Excel - Standard functions reference file

# Excel - Standard functions file

Standard operators (e.g. + - / *) plus basic functions (e.g. SUM, MIN, MAX, AVERAGE) plus other generic functions cover most MS Excel user needs. The reference file contains tens of working examples of those other generic functions and more advanced random number generation.

## 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  