TBM & Dynamic Arrays

TBM, Dynamic Arrays, LAMBDA, Power Query, Data Model, & Power Pivot

This series explores integrating Excel's major features into one model that expands to accommodate as many months and units as needed without modeler intervention or VBA.

About the Example

The completed example model used in this series can be downloaded from here.

This example model simulates an apartment complex where the owners want to project how much rent they will collect in the coming months. The owners can enter when the model starts, how many months to project, and the annual rent increase. For each unit, the owner can enter: the unit's number; rent start date; and initial rent. And here is the fun part. The owners can add as many units as they like WITH NO MODEL CHANGES! Just type the unit number/ID just after the last unit in the Unit: row (row 9).

Links in this series

Rents: Formulas (this page)
Rents: PQ, DM, PP (soon)

The Magical Formula

The secret to this minor feat of magic is In cell B14. B14 holds one formula that handles as many months and as many units as needed. At top right is the raw formula which, admittedly, is complicated. Below right is a slightly simpler version using one of my standard LAMBDAs to calculate differences in months.

Simplicity and Complexity

So let me first state that the simplicity of having one formula in one cell handling thousands of calculations more than offsets the complexity of the formula. The one-cell-approach means:

There is no possibility of inconsistencies in thousands of calculated results.
Auditing is limited to a single cell
And did I mention, no modeler intervention needed to add unit and/or months.

Understanding LET()

LET() to many is an unknown so for those readers, here is a brief explanation.

LET() has two major parts: variable declarations and the final function.

LET() starts with the variable declarations. It can have as many variables as we like. Each variable declaration starts with the variable's name, followed by the variable's definition.

In the example, the second variable's name is Seq. Seq's definition is a formula: SEQUENCE(Months)-Offsets.

Variable definitions can be values or formulas.

NOTE! The new lines and spacing is totally optional. I add new lines and spaces to help make LET() easier to read.

The last section of a LET() is its formula. The formula uses the variables defined above it, calculates a result, and LET() returns that result.

Understanding the Magical Formula

Now that we understand the basics of LET(), let us look at our magical formula's variables and formula.

Offsets:

The first variable holds the number of months between model start and rent start. If the rent starts two months after the model start, Offsets will equal 2. If the rent starts three months before the model start, Offsets will equal -3.

In the non-LAMBDA version, the formula is:

(YEAR(Rent_Starts)-YEAR(Model_Start)) * MPY + (MONTH(Rent_Starts)-MONTH(Model_Start))

Function YEAR() provides the date's year and MONTH() provides the date's month. MPY holds Months Per Year which is 12.

Seq:

The second variable holds a dynamic array with as many rows as requested starting with Offsets' value. If 60 months are requested, SEQ will contain 60 numbers. If Offset equals -3, then SEQ will start at -3 and end at 60-3.

This variable's formula is:

SEQUENCE(Months)-Offsets

Function SEQUENCE() provides a dynamic array containing incrementing numbers. In this case the array starts at 1 and increments until it hits the value in Months. From that we subtract the month offsets: Offsets.

Period:

The third variable holds the year number for each SEQ value. Rents change on the anniversary date, not each month. Its formula is:

QUOTIENT(Seq-1,MPY)

Function QUOTIENT() returns the whole number in a division. It is equivalent to INT((Seq-1)/12).

FV(Annual_Increase, Period, 0, -(SEQ>0) * Rent_Amount)

This is our LET() formula. It uses Excel's FV() (Future Value) function to calculate the Rent_Amount for each period and increasing it each year. Our formula leverages an oddity in Excel where TRUE = 1 and FALSE = 0 so when SEQ is greater than zero, FV()'s Pv parameter (present value) will be the Rent_Amount times 1; otherwise, it will be Rent_Amount times 0, or just plain zero.

NOTE! SEQ is a dynamic array which makes, Period is a dynamic array. Period make this formula's result a dynamic array with as many rows as are in Period. And if Rent_Amount is a horizontal dynamic array, our formula's result will have as many columns as are in Rent_Amount.

Understanding the Names

Our formula uses a lot of names. Names make formulas self-documenting which helps make them more readable.

The gray shaded areas are our user inputs and contain names. The first three names (Model_Start, Months, Annual_Increase) are created using Excel's Formulas > Create from Selection menu option. Thus, each value's name matches the label to its left. The next set of names are a bit more complicated.

MPY

This is a mnemonic for Months Per Year. It holds the literal 12. I use names to hold literals and use the names in formulas to help make formulas self documenting. MPY is only accessible through the name manager. Normally, I place such values in property tables and name them using Formulas > Create from Selection menu option. But this model is very small so I decided to bend that rule ... this time.

Units

This calculates how many entries are in the Unit: row (row 9). It is a named formula that can only be accessed in the name manager. Its formula is:

=MATCH(TRUE, ISBLANK(OFFSET($B$9, 0, 0, 1, 9999)), 0) - 1

The formula looks for the first blank cell starting in $B$9, which is the first unit entry, and extending out 9999 columns. This assumes there will never be more than 9999 units which, for Excel, is probably more than appropriate. Here is a bit more detail about this formula.

OFFSET() returns a range of cells starting at $B$9 and continuing for 9999 cells to the right.

ISBLANK() returns TRUE for each cell in the OFFSET() range that is blank and FALSE for each cell that is not blank.

MATCH() returns the position of the first cell containing TRUE in ISBLANK().

Unit

This is a dynamic named range holding all Unit numbers/IDs. Its formula is:

=OFFSET($B$9, 0, 0, 1, Units)

Rent_Amount

This is a dynamic named range holding all initial rental amounts. Its formula is:

=OFFSET($B$10, 0, 0, 1, Units)

Rent_Starts

This is a dynamic named range holding all initial rent start dates. Its formula is:

=OFFSET($B$11, 0, 0, 1, Units)

Understanding LAMBDA & MonthDiffλ

We can simplify our formulas and our lives by using LAMBDA(). LAMBDA() is a formula with defined parameters. At top right is our magical formula with a named LAMBDA: MonthDiffλ. A named LAMBDA is like any other named formula. It has a name (MonthDiffλ) and a formula. Let us look more closely at MonthDiffλ's formula to understand what a LAMBDA is and how this works.

At bottom right is MonthDiffλ's formula. It starts with the special function LAMBDA(). Like LET(), LAMBDA() starts with variable declarations, but unlike LET(), LAMBDA's variables are just variable names with no definitions. What they are is determined by what we send to the LAMBDA.

Let us look again at the top right figure and in particular, look at how MonthDiffλ is referenced.

=MonthDiffλ(Rent_Starts,Model_Start)

Rent_Starts is the first variable and Model_Start is the second. Our LAMBDA stuffs these two values into its variables declarations; thus, Date1 = Rent_Starts and Date2 = Model_Start .

Now going back to the bottom figure, we see Date1 and Date2 used in the formula which starts after our two variables and is the last section of our LAMBDA. The formula uses the two variables and our LAMBDA returns that result.

Why use LAMBDA()?

As mentioned, we can simplify formulas using LAMBDA. But more importantly, we can simplify our lives by using pre-built, pre-tested formulas. In this case, I have had a few projects that need to calculate the difference in whole months. The Excel function that does that is called DATEDIF(). Microsoft has deprecated that function. It is provided only for backward compatibility because it can produce inconsistent results under certain situations, such as the second date being before the first date, which can happen in this model. Another option is YEARFRAC(). YEARFRAC() is not designed to return whole month values.

With no solid options available directly in Excel, I created my own function using LAMBDA. I placed it in my GitHub Gist repository so I can load it into my projects whenever I need it.

Conclusion

In this section we saw how a single dynamic array formula can replace thousands of conventional formulas. It can auto-extend without any formula changes or modeler intervention. It can make our models more robust by eliminating any potential for formula inconsistencies and that can reduce time to create and audit our work.

In the next section we will see how to incorporate dynamic arrays into interactive analytics using Power Query, the Data Model, and Power Pivot.

Page updated

Google Sites

Report abuse