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.

Financial Modeling Academy

  • LinkedIn App Icon
  • Wix Facebook page
  • Wix Twitter page