﻿ MS Excel - Functions reference file

# MS Excel - Functions reference file

Standard operators (e.g. + - / *) plus basic functions (e.g. SUM, MIN, MAX, AVERAGE) plus other generic functions cover most MS Excel user needs. Below, you can download a handy reference file with examples for those other generic functions.

## 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 - multifunctional SUBTOTAL
• Example 9: Dynamic sorting or finding nth largest or nth smallest value - LARGE and SMALL
• Example 10: Avoiding additional columns - SUMPRODUCT
• Array formula intermezzo *
• Example 11: Connecting a second table with multiple columns to a first table**: MATCH versus VLOOKUP (and HLOOKUP)
• Example 11B: fast VLOOKUP
• 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  