Lambda formulas in Excel

excel
intermediate
Published

July 15, 2024

Session materials

Previous attendees have said…

  • 25 previous attendees have left feedback
  • 96% would recommend this session to a colleague
  • 92% said that this session was pitched correctly

Three random comments from previous attendees
  • Excellent. Was not aware at all of this new function - super useful. I think something about relative cell referencing would be useful in it’s application.
  • Interesting session. Not sure if I will need to use the knowledge anytime soon but good to know for any future requirements.
  • I covered this in the 5 week Excel training sessions a while back but wanted a refresher session to try to understand it a bit more. I found it a good refresher session although it was a bit frustrating when people were struggling with not having access to the chat or having a correct enough version of Excel. I wish people would take more note of all the info which was shared in advance to allow the sessions to run more to time. This wasn’t Brendan’s fault and as always he tried to help and include everyone

Welcome

  • 🌶🌶 this session is for intermediate Excel users
  • you’ll need M365 Excel (either web or desktop) to follow along
    • earlier versions of Excel don’t have the LAMBDA function that we’ll need
    • you’ll also need to be pretty fluent with formulas, and know a bit about naming things in Excel

Session outline

  • this session is based on our Excel skill-builder course
  • quick introduction to lambda formulas
  • LAMBDA()
  • two practical examples
  • use-cases, and tips for applications

Introduction

  • lambda formulas are a way of repeating complicated Excel formulas safely
  • fairly new - M365 Excel only
  • odd, rather than hard to implement

LAMBDA

Microsoft’s summary of LAMBDA

  1. take an existing formula
  2. work out what the inputs are - usually, which cells are referenced
  3. add one parameter per input
  4. do a calculation with those parameters
  5. name and save that formula

Making a lambda function (setup)

  • put some random numbers in A3 and B3
  • we could calculate A3 * B3 directly
  • but imagine we want to convert = A3 * B3 to a lambda

Making a lambda function

  • start with the lambda outline in D3
    • = LAMBDA()
  • next, add test values in a second set of brackets
    • = LAMBDA()(A3, B3)
  • we now invent parameter names (like variable names) for each of those values, and add them
    • = LAMBDA(n_1, n_2, )(A3, B3)
  • then add the formula that we want to replicate
    • = LAMBDA(n_1, n_2, n_1 * n_2)(A3, B3)

Saving a lambda function

  • once you’ve tested and checked your lambda with a range of test values, copy your lambda formula (without the test values in the second brackets)
  • then open the name manager (Ctrl + F3)
  • create a new name, then name your lambda definition using the New Name interface
  • paste your lambda into the Refers to: section
    • omit the test values - so just = LAMBDA(n_1, n_2, n_1 * n_2)
  • test your new lambda function

Lambda functions

  • a real-ish example: converting CHI to DoB
  • put an example CHI into G2 (like 1610790854)
  • you can use the following 😱 formula:
=DATE(IF(RIGHT(LEFT(G2, 6), 2) > RIGHT(YEAR(TODAY()), 2), 19, 20) & RIGHT(LEFT(G2, 6), 2), MID(LEFT(G2, 6), 3, 2), LEFT(LEFT(G2, 6), 2))
  • that’s a horrifying thing to paste about, so we can make this into a lambda:
=LAMBDA(chi, DATE(IF(RIGHT(LEFT(chi, 6), 2) > RIGHT(YEAR(TODAY()), 2), 19, 20)&RIGHT(LEFT(chi, 6), 2), MID(LEFT(chi, 6), 3, 2), LEFT(LEFT(chi, 6), 2)))(G2)
  • then save as CHI_TO_DOB via the name manager

Use-cases, and tips for applications

  • making workbooks less complicated
    • names and lambda formulas are a strong combo
  • standardising (and user-proofing) complex formulas
  • lambdas are local, so you need to think laterally to re-use them
    • make a new blank sheet, and right-click the sheet tab
    • Move or Copy... to new book