Lambda – For Flexible Formulas
Lambda is integral to our work at The New Excel because of the flexibility it adds. Lambda opens the door to a full, multiline programming environment – that supports documentation – right on the worksheet side of Excel. The lambda workspace engenders flexibility in calculations, formulas, and reporting. In this post, we’ll look at how the structure lambda provides makes it easy to adjust, adapt, and clone formulas.
Project Overview
I have a client who provides holiday lighting services. They create stunning outdoor lighting displays and provide elegant indoor and outdoor decorations, including professionally-decorated trees, wreaths, and garlands. Their business runs through two cycles: installation and takedown. Each of those two cycles features multiple legs.
The installation cycle has four legs: 1) installing any lighting displays, 2) installing any décor displays, 3) turning on the lights for any lighting displays, and 4) turning on the lights for any décor displays. Because of customer needs and the size and quantity of jobs, these four legs may not all occur on the same day. On top of that, most jobs require a follow-up visit to complete the work.
How do we preserve sanity and manage all these dates, jobs, and hours?
Enter Lambda
I created a double-lambda solution to generate a fully automated calendar. The first lambda, ManHourCalendar, creates a dynamic grid of the man hours designated for a given day or week. This grid includes customer name, quote number, city/zone, and man hour totals for each kind of work to be done. Color coding indicates which leg(s) of the install cycle will be performed then (e.g. lighting, décor, lights on).
The second lambda, ManHourCalendar_Stack, invokes the first lambda, and stacks the results, for each day (or week) my client wishes to display. This allows them to see the work for a single week, a month, or even the entire season. With this tool, they managed their staffing, their customers, and their workload. Sanity preserved.
Round 2 – Takedown
Now, with the holidays behind them, attention turns to taking down all these decorations. It’s the install process in reverse. The takedown cycle has three legs: 1) turning off the lights, 2) removing any lighting displays, and 3) removing any décor displays.
Here’s the problem: we built the man hour calendar for install this season; we don’t have a man hour calendar for takedown. The solution? Re-enter lambda.
Because the work of install and takedown is similar in structure, I duplicated the ManHourCalendar lambda for use with takedown. All the necessary dates and data are stored in an Excel Table, which the ManHourCalendar lambda references. For my new ManHourCalendar_Takedown lambda, I replaced the references to the install columns with references to the takedown columns in the data Table. And, with just a few other minor adjustments, the new takedown lambda was done.
To complete the revision, I added a parameter on the worksheet for my client to toggle between Install and Takedown mode. As the final step, I adjusted the ManHourCalendar_Stack lambda to invoke the correct lambda, based upon that parameter. Voila! A takedown calendar.
Final Thoughts
Getting the initial man hours calendar where my client needed it to be took about fifteen hours. Modifying the lambdas to create a takedown calendar: 1 hour!
Why? Because lambda engenders flexibility in formulas. I built a structure through which their data flowed to create the output they needed. I was able to use (almost all) of that same structure with different data to create an entirely new report.
New source. Same pipes. New output.
That is the flexibility of lambda.
