5g: ScheduleValuesλ()
Description
ScheduleValuesλ() sums values from a table (or array) per a timeline's periods. This can be used to schedule asset acquisitions, project expenditures, VC funding, etc.
Syntax
ScheduleValuesλ(PeriodStarts, PeriodEnds, ScheduleDates, Values, [Diagnostics]))
PeriodStarts (Required)
A column or row of period start dates (timeline).
PeriodEnds (Required)
A column or row of period ends dates (timeline).
Values (Required)
A column or row of values to be scheduled.
EffectiveDates (Required)
A column or row of when a value is scheduled.
Diagnostics (Optional)
If TRUE, input errors that can occur after formula entry will display as diagnostic messages. The default is FALSE. It is recommended that models have a named range called Diagnostics and this named range be set to TRUE during model development and then set to FALSE when the model is placed into production.
Remarks
EffectiveDates can be in a table, a column array, or a row array. Whatever orientation is used for EffectiveDates, must be used in Values.
When using horizontal timelines and a table for EffectiveDates and Values, this formula will achieve the same result:
=SUMIFS(Values, ScheduleDates,">=" & PeriodStarts, ScheduleDates,"<=" & PeriodEnds)
Example
