LAMBDA: ScheduleValuesλ

Code

This is the code as of this writing. This is a good example of our LAMBDAs can help beginners.

The formula in this LAMBDA is a fairly simple. Those with intermediate Excel skills may prefer typing XLOOKUP() instead of this LAMBDA. But for those starting out, this LAMBDA takes a generic function (XLOOKUP) and, by the LAMBDA's name, communicates its use. So when someone with limited experience searches for a function to schedule rates, when they see this LAMBDA's name, they will know it applies to their need. When the see XLOOKUP, they may not connect that name with this purpose and instead of scheduling amounts, they fall back on typing them in by hand and suffer the tedium and, potentially, associated errors.

I intend to update this when Microsoft implements HSTACK. So for the most current version, see the Gist.

/*

FUNCTION NAME: ScheduleValuesλ

DESCRIPTION: Schedules values in a timeline from a schedule in a table.

EXAMPLE: =λScheduleValues(Timeline, tblRates(Start), tblRates(Values))

Many models have manually entered values in each cell under a timeline

which either never change, or change infrequently. This is tedious and

error prone. Instead, enter only the dates that a value changes and the

new value on that date, then use ScheduleValuesλ to, using a dynamic array,

place values for us.

Need additional dates? Just add them to the table.

Want to change a value or date? Just change it in the table.

ARGS:

Timeline An horizontal range or dynamic array holding the model's timeline

Starts A table column or array of dates when values take effect

Values Table column or array of values that change with dates

GUILTY PARTIES: Craig Hatmaker 2022

*/

ScheduleValuesλ = LAMBDA(

// Parameter Declarations

Timeline,

Starts,

Values,

// Function starts here

XLOOKUP(Timeline, Starts, Values, "", -1)

);