**Excel functions**

## The objective of this section is to provide the key Microsoft Excel functionsthat will allow you to build optimized financial models. There are hundreds of other functions but the selection below should be enough in the vast majority of the cases.

## SUM

## Sums all the cells within a range

## Syntax:=sum(Range)

Using Excel in a different language? Find the translations of the functions here.

SUMIF

## Sums all the cells that verify a specific condition

## Syntax:=sumif(Range of the condition, Condition, Range of the values)

SUMPRODUCT

**Multiplies corresponding array components and sums them**

Syntax:=sumproduct(array #1 , array #2, etc.)

The array must have exactly the same size (number of cells)

MIN/MAX

Returns the maximum (minimum) value from a range

## Syntax:=min(range) or min(cell #1 , cell #2, etc.)

IF

Returns a value if a condition is True and another value if it is False

Syntax:=if(condition , value if condition is True , value if condition is false)

In the example below, the if function return the value if the year is inferior to 2021 and na else.

IFERROR

Returns a specific value if the tested formula returns an error, else it returns the formula result

Syntax:=iferror(formula , value if the result is an error)

MATCH

## Returns the relative position of the cell containing a specific value within a range

## Syntax:=match(Value to look for, Range, Type (always put 0))

CHOOSE

## Returns one value from a list of values

## Syntax:=choose(Value number , Value #1 , Value #2 , etc)

## It is very powerful when used with match as it allows to switch very quickly from one scenario to the other

INDEX

## Returns one value from one array

## Syntax:=index(Array , Row # , column #)

## It is very powerful when used with match

OFFSET

## Returns a cell value or a range from a specific range

## Syntax:=offset(Reference , Numbers of rows up or down vs reference , Numbers of column left or right vs reference , Height of the range in numbers of cells , width of the range in number of cells)

## It is very powerful when used with match to create dynamic ranges

## It can be used like INDEX to return a signe value:

But it can also be used to create a dynamic range, i.e. a range that does not have a fixed size:

Using Excel in a different language? Find the translations of the functions here.