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.
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.
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.