Functions

Introduction to Functions in Models

Overview

Models supports many of the Functions that you are used to using in Excel, from logical functions like IF() and AND(), to financial functions like XNPV() and IRR().

Example of an IF function in Models

In fact, all Models functions are Excel functions, meaning you can always generate working Excel spreadsheets from your Models.

You can also create custom functions using the LET/LAMBDA features, which are explained in the Advanced Functions section.

Not all Excel functions are fully supported in Models. You can still use non-supported functions to generate working Excel spreadsheets, but they will not show up in the AutoComplete dropdown, and Models will not be able to calculate values.

See the full list of supported functions below.

Entering a Function

The simplest way to create a Function is to simply type the Function name in the Formula Entry Bar.

Typing the name followed by an opening parentheses will create the relevant Calculation Parts, or you can select the Function from the AutoComplete dropdown - functions are indicated by the function symbol.

As in Excel, Function arguments are separated by commas.

We have not yet implemented an Intellisense equivalent for Function arguments (i.e. where the user interface indicates the name and purpose of each argument).

Please refer to Excel documentation or test the Function from within Excel to see the required arguments for an unfamiliar Function.

List of supported Functions

See Excel documentation for full list of Excel functions.

Name	Excel Category	Microsoft Documentation
DATE	Date and time	https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349
DAY	Date and time	https://support.microsoft.com/en-us/office/day-function-8a7d1cbb-6c7d-4ba1-8aea-25c134d03101
EDATE	Date and time	https://support.microsoft.com/en-us/office/edate-function-3c920eb2-6e66-44e7-a1f5-753ae47ee4f5
EOMONTH	Date and time	https://support.microsoft.com/en-us/office/eomonth-function-7314ffa1-2bc9-4005-9d66-f49db127d628
MONTH	Date and time	https://support.microsoft.com/en-us/office/month-function-579a2881-199b-48b2-ab90-ddba0eba86e8
WEEKDAY	Date and time	https://support.microsoft.com/en-us/office/weekday-function-60e44483-2ed1-439f-8bd0-e404c190949a
YEAR	Date and time	https://support.microsoft.com/en-us/office/year-function-c64f017a-1354-490d-981f-578e8ec8d3b9
YEARFRAC	Date and time	https://support.microsoft.com/en-us/office/yearfrac-function-3844141e-c76d-4143-82b6-208454ddc6a8
IRR	Financial	https://support.microsoft.com/en-us/office/irr-function-64925eaa-9988-495b-b290-3ad0c163c1bc
XNPV	Financial	https://support.microsoft.com/en-us/office/xnpv-function-1b42bbf6-370f-4532-a0eb-d67c16b664b7
AND	Logical	https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9
IF	Logical	https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
IFERROR	Logical	https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611
NOT	Logical	https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77
OR	Logical	https://support.microsoft.com/en-us/office/or-function-7d17ad14-8700-4281-b308-00b131e22af0
FILTER	Lookup and reference	https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
INDEX	Lookup and reference	https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd
CEILING	Math and trigonometry	https://support.microsoft.com/en-us/office/ceiling-function-0a5cd7c8-0720-4f0a-bd2c-c943e510899f
CEILING.MATH	Math and trigonometry	https://support.microsoft.com/en-us/office/ceiling-math-function-80f95d2f-b499-4eee-9f16-f795a8e306c8
EXP	Math and trigonometry	https://support.microsoft.com/en-us/office/exp-function-c578f034-2c45-4c37-bc8c-329660a63abe
FLOOR	Math and trigonometry	https://support.microsoft.com/en-us/office/floor-function-14bb497c-24f2-4e04-b327-b0b4de5a8886
FLOOR.MATH	Math and trigonometry	https://support.microsoft.com/en-us/office/floor-math-function-c302b599-fbdb-4177-ba19-2c2b1249a2f5
LN	Math and trigonometry	https://support.microsoft.com/en-us/office/ln-function-81fe1ed7-dac9-4acd-ba1d-07a142c6118f
LOG	Math and trigonometry	https://support.microsoft.com/en-us/office/log-function-4e82f196-1ca9-4747-8fb0-6c4a3abb3280
MOD	Math and trigonometry	https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3
PI	Math and trigonometry	https://support.microsoft.com/en-us/office/pi-function-264199d0-a3ba-46b8-975a-c4a04608989b
POWER	Math and trigonometry	https://support.microsoft.com/en-us/office/power-function-d3f2908b-56f4-4c3f-895a-07fb519c362a
PRODUCT	Math and trigonometry	https://support.microsoft.com/en-us/office/product-function-8e6b5b24-90ee-4650-aeec-80982a0512ce
RAND	Math and trigonometry	https://support.microsoft.com/en-us/office/rand-function-4cbfa695-8869-4788-8d90-021ea9f5be73
ROUND	Math and trigonometry	https://support.microsoft.com/en-us/office/round-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c
ROUNDDOWN	Math and trigonometry	https://support.microsoft.com/en-us/office/rounddown-function-2ec94c73-241f-4b01-8c6f-17e6d7968f53
ROUNDUP	Math and trigonometry	https://support.microsoft.com/en-us/office/roundup-function-f8bc9b23-e795-47db-8703-db171d0c42a7
SQRT	Math and trigonometry	https://support.microsoft.com/en-us/office/sqrt-function-654975c2-05c4-4831-9a24-2c65e4040fdf
SUM	Math and trigonometry	https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89
SUMPRODUCT	Math and trigonometry	https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e
AVERAGE	Math and trigonometry	https://support.microsoft.com/en-us/office/average-function-047bac88-d466-426c-a32b-8f33eb960cf6
MAX	Math and trigonometry	https://support.microsoft.com/en-us/office/max-function-e0012414-9ac8-4b34-9a47-73e662c08098
MIN	Math and trigonometry	https://support.microsoft.com/en-us/office/min-function-61635d12-920f-4ce2-a70f-96f202dcc152
LET	Other	https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999
LAMBDA	Other	https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67