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.