🎓
Models University
  • Alpha Testing
    • 👋Welcome to Alpha
    • Installation and Setup
    • Support and Help
    • Terms and Licence
  • Start Here
    • What is Models?
    • Quick start
    • Tutorials
  • Using Taglo Software
    • The Taglo Interface
      • Navigating in Models
      • List Views
      • Adding Items
      • Deleting Items
      • Searching and Filtering
      • Previewing and Editing
      • Using Wizards
      • Save/Undo/Redo
      • Keyboard Shortcuts
    • Data Storage
    • Namespaces
    • Inbuilt data
  • Tags
    • Introduction to Tags
    • Tag Basics
    • Tags Applet
    • Advanced Tags
  • Models
    • Home Tab
      • Creating a Model
        • Timings
      • Version History
      • Application Settings
    • Model Editor
  • Variables
    • Variable Basics
      • Variable Setup
      • Unit and Calculations
    • Time Segments
      • Time Segment Setup
    • Summaries
    • Default Assumptions
  • Units
    • Introduction to Units
    • Key Unit Concepts
    • Advanced Units
    • Defining a Unit
    • Unit Factors
  • Dimensions
    • Introduction to Dimensions
    • Dimension Setup
    • Defining Unit Variance by Dimension
  • Data Types
    • Data Types
  • Assumptions
    • Introduction to Assumptions
    • Creating/Referencing Assumptions
    • Assumptions Wizard
      • Assumption Dates Step
      • Assumption Values Step
      • Linking to external Data
      • Linking to Excel Inserts
      • Sensitivity Values
  • Calculations
    • Introduction to Calculations
    • The Calculation Page
      • The Formula Entry Bar
      • Unit Analysis
    • Calculation Parts
      • Variable References
        • Referencing Time
        • Referencing Dimensions
        • Lookups
        • Summarising Variables
      • Period References
      • Functions
        • Advanced Functions
  • Aggregation
    • Introduction to Aggregation
    • Creating Aggregations
  • Rearranging
    • Introduction to Rearranging
    • Rearranging Example
    • Rearranging Tips
  • Cases
    • Introduction to Cases
    • Managing Scenarios
    • Managing Sensitivities
  • Layout
    • Generating Excel
    • Auto Layout
    • Sheets
      • Sheet Content
      • Adding and Editing Content
    • Excel Formatting
      • Excel Themes and Styles
      • Line Formatting
    • Data Tables
    • Excel Generation Troubleshooting
  • Excel Inserts and Outputs
    • Excel Inserts
    • Excel Outputs
  • Number Formats
    • Number Formats
  • Dashboards
    • Dashboards Basics
    • Dashboard Layout
    • Dashboard Items
    • Excel Dashboards
  • Metrics
    • Metrics
  • Charts
    • Introduction to Charts
    • Chart Customisation
  • Importing Models
    • Importing Models
  • Data
    • Introduction to Data
    • Creating Data
    • Data Sources
Powered by GitBook
On this page
  • Overview
  • LET functions
  • LAMBDA functions
  • Creating a Lambda
  • Using a Lambda function
  1. Calculations
  2. Calculation Parts
  3. Functions

Advanced Functions

How to create custom functions using LET and LAMBDA

PreviousFunctionsNextIntroduction to Aggregation

Last updated 11 months ago

Overview

Microsoft has added two powerful features in recent years which effectively allow users to create custom functions.

There are many benefits to using these functions, including:

  • Improved readability of formulas

  • Minimising the number of references to Calculation inputs

  • Making complex functions reusable

To understand how LET and LAMBDA functions work, we recommend reading the Microsoft documentation. Links are included in the relevant sections below.

LET and LAMBDA functions are available in Office 365 versions of Excel. If you are using older versions of Excel, you can still use these functions within Models, but they will not produce working Excel spreadsheets.

LET functions

If you are new to LET functions, first read the

To create a LET function, you can type the function into the , as you would for any other function, with two key differences: Arguments (called "variables" in the Microsoft documentation) and References to those arguments must be entered via the AutoComplete dropdown. This is demonstrated in the example below.

Let's say we want to create a LET function to find one of the roots of a quadratic equation, as shown above. This is useful as the arguments a and b are both used twice in the formula, so using a LET function means we only have to enter the input values for those arguments (which could be references to Variables) once.

LAMBDA functions

Creating a Lambda

When you create a new Lambda you will need to perform the following steps, as per the screenshots below:

  • Select a Namespace

  • Create the Arguments for the function (2)

  • Enter the formula (3)

Lambdas can reference other Lambdas - use this to greatly simplify complex calculations

Using a Lambda function

Once you have created a Lambda function, it can be used in Models just like any other Function, as long as the Lambda's Namespace is available in your Model.

For instance, to use the QuadraticRoot Lambda from the above example, we would simply type the name of the Lambda into the Formula Entry Bar, select the function from the dropdown, and enter arguments as desired, as shown below.

When we enter each of the arguments (a, b, and c), we type in the name of the argument and then select the Argument Calculation Part with the plus in braces symbolfrom the AutoComplete dropdown, as shown below.

To reference these arguments, we type in the name of the argument, and select the Argument Reference Calculation Part with the comma in braces symbol , as shown below.

If you are new to LAMBDA functions, first read the

To create a LAMBDA function, go to the Lambda applet, which is accessible from the , or create one .

Lambdas are stored separately from Models so can be used in any Model that has access to the Lambda's .

Choose a Name (1). The name must obey Excel naming rules for , and not include spaces or certain special characters. The full rules are complex so we will not list them here, but the Lambda dialog will not let you proceed if the name does not meet the rules.

The Calculation entry step for a Lambda uses the same as Variables, but with reduced capabilities (e.g. as Lambdas exist outside Models, you cannot reference Variables or Periods in a Lambda formula).

Microsoft documentation here
Namespace
Named Ranges
Formula Entry Bar
Microsoft documentation here
Formula Entry Bar
Navigation Bar
on the fly from the Formula Entry autocomplete menu
A LET function to find a root of a quadratic equation
Creating a LET argument
Referencing a LET argument
Creating a Lambda
Using a Lambda function in a Calculation