From Formulas to LAMBDAs
Demystifying LAMBDA

From Formulas to LAMBDAs

I have just completed creating a small class on converting a group of formulas into a single LAMBDA function. It is available for download from the link at the bottom.

Why Convert a Group of Formulas into a Single Function?

Throughout my career, I have often been tasked with calculating values that require several steps, each involving one or more formulas. The intermediate results of these formulas are usually not of particular interest; only the final calculation matters. To illustrate, think of long division.

Long division

In long division, we show each step, but the result of each step is important only for obtaining the final answer. Similarly, when we use Excel or a calculator app to divide numbers, we do not see the interim results—only the final result, which is all we are interested in.

If we need to calculate something new that we will only calculate once, doing it the long way allows us to check each step for correctness. However, if we need to repeat complex calculations, it can be beneficial to create a function that, like any Excel function, performs all the calculations internally and displays only the final result.

Class Overview

This class uses the example of a fictitious solar farm company that needs to calculate the electricity generated by each installation. The calculation considers the rated annual output, modified by seasonal factors, and adjusted for performance degradation due to aging over 30 years. In our example, the customer needs 24 individual installations, implemented monthly over a two-year period. The calculations are somewhat complex and need to be performed for each new customer. Our fictitious company could use a simple function to produce the final results.

The class takes you step-by-step from creating the traditional formulas to grouping them into a LET() function, and finally wrapping the LET() function into a named LAMBDA.

Here is the class: https://www.dropbox.com/scl/fi/fsywqvh2rr990op64fkyi/Solar.pdf?rlkey=bdl80qd7nkn121idpy1u97gl5&dl=1

Erik Oehm

Excel Robot

8 个月

I love the long division example! People take for granted the power of functions. Once you get used to using them for common repeatable tasks, you never want to go back. And they become trusted building blocks for getting real work done in less time with less complexity.

要查看或添加评论,请登录

Craig Hatmaker的更多文章

  • Rollingλ(): 5g Function for Rolling Calculations

    Rollingλ(): 5g Function for Rolling Calculations

    Introduction Excel's LAMBDA functions have opened up incredible possibilities. 5g functions are LAMBDA functions…

    10 条评论
  • TRIMRANGE() and Trim Refs

    TRIMRANGE() and Trim Refs

    I cringed when I first saw TRIMRANGE(). "Oh great," I thought, "another reason for people to avoid learning tables.

    6 条评论
  • Intro to LAMBDA - Lesson 16 - Simple Corkscrew

    Intro to LAMBDA - Lesson 16 - Simple Corkscrew

    I could use your help. I am preparing a virtual work session for FMI on "Intro to LAMBDA.

    13 条评论
  • Programming with LAMBDA - Prime Numbers

    Programming with LAMBDA - Prime Numbers

    LAMBDA makes Excel "Turing Complete." Virtually all programming languages are Turing complete.

    2 条评论
  • Timing LAMBDAs with LAMBDA

    Timing LAMBDAs with LAMBDA

    I recently created a 5g function. It worked great but two friends suggested faster ways to accomplish the same thing.

    2 条评论
  • Create a 5g Function: RunTotRowsλ()

    Create a 5g Function: RunTotRowsλ()

    NOTE! This article was written with assistance from Google's Gemini AI. Introduction In the world of Excel, complex…

    7 条评论
  • Live 5g Instruction

    Live 5g Instruction

    The only live 5g training session starts July 30th. Register here: https://maven.

    4 条评论
  • Stairway to ... LAMBDA?

    Stairway to ... LAMBDA?

    I use Excel for everything. I need some stairs from my yard to the forest floor below.

    19 条评论
  • Corkscrew LAMBDA Template

    Corkscrew LAMBDA Template

    Create complex corkscrew calculations easily with this template. What is the difference between complex Corkscrews and…

    3 条评论
  • LAMBDA and Excel's Secret Function: EVALUATE()

    LAMBDA and Excel's Secret Function: EVALUATE()

    EVALUATE() is a hidden Excel function. When we try to use it, we get: EVALUATE() has been around since 1992 when…

    20 条评论

社区洞察

其他会员也浏览了