Excel Functions
There are over 450 functions in Excel and this number is constantly growing as the Microsoft team expand the Excel function library.
We have filtered this list of Excel functions down for you to provide you with the key functions that you need to know as a modeller.
| SUMIFS | The SUMIFS function returns the total value for rows in a dataset that meet the criteria that you have defined. SUMIFS is an extremely useful function for obtaining the values to use in your reports. |
| XLOOKUP | In modelling we use the XLOOKUP function to map data from one table into another based on a unique reference. |
| IF | The IF function performs a logical test and then returns the value you have provided based on a TRUE or FALSE result. In financial modelling it is often used to model business logic based on 1, 0 flags. |
| IFS | The IFS function performs a series of logical tests and then returns the value you have provided based on a TRUE or FALSE result. The IFS function is an upgrade of the IF function. It allows financial modellers to test multiple arguments without needing a complicated nested IF formula. |
| INDEX | We use the INDEX function to return a value from a range based on the position specified. In scenario modelling we can use INDEX to pull the values for the selected scenario. INDEX is often combined with the MATCH function to to map data from one table into another based on a unique reference. |
| EOMONTH | The EOMONTH function returns the date value for the last day of the month plus or minus the number of months you have specified. |
| TEXTSPLIT | The TEXTSPLIT function splits values from a cell across rows and/or columns based on a delimiter. The function makes use of Excel' dynamic array functionality to "spill" the results aross rows and columns. |
| TEXTJOIN | The TEXTJOIN function concatenates (or joins) values together from cells or ranges of cell. |
| TEXTBEFORE | The TEXTBEFORE function returns the text from a text string that occurs before a given delimiter (character or string). |
| TEXTAFTER | The TEXTAFTER function returns the text from a text string that occurs after a given delimiter (character or string). |
| SWITCH | The SWITCH function evaluates an expression against a given value and returns the corresponding result value that you have provided. |
| MIN | The MIN function returns the smallest value in the range of numbers provided. In financial modelling the MIN function is very useful for ensuring that the result of a calculation does not go above a given threshold. |
| MAX | The MAX function returns the largest value in the range of numbers provided. In financial modelling the MAX function is very useful for ensuring that the result of a calculation does not go below a given threshold. |
