Embedded Files
Description
Thunk and SCAN is a much faster alternative to SCAN and STACK but is the most difficult to understand. A 'thunk' in this context is a function we can use to hold arrays which we can leverage to get around Excel's "Array of Arrays" error.
Array of Arrays error
In this context, SCAN produces a row of values. That row is an array. SCAN is limited to one row. If we try to use SCAN to output two rows, that looks to Excel as two arrays inside SCAN's one array. Excel cannot handle two arrays inside one array and so it throws an Array of Arrays error.
Thunks
A thunk is a function that can hold, among other things, an array. Because Excel thinks of functions as one object, we can pass an array of Thunks, where each Thunk contains an array, to functions that would otherwise not allow an array of arrays.
To me, this is not intuitive but I will do my best to explain it simply.
Learning Objectives
At the end of this course, learners will know:
The basics of Thunk and SCAN
What a Thunk is.
How to load a Thunk with an array
How to load an array with Thunks
How to retrieve values from a Thunk
Prerequisites
Understanding of the 5g basic source structure template.
Microsoft's free add-in: Advanced Formula Environment (AFE)
Discussion
This template is at least 10x faster than SCAN and STACK though hard to understand.
If a SCAN and STACK function performs poorly, we can sometimes convert it to this template to speed things up. The major difference here is we will retrieve period values from an array of Thunks instead of using INDEX against period arrays directly. This result in much faster calculations because instead of using INDEX to pull from a larger array, we will use INDEX to pull from a much smaller array - a Thunk containing two values.
What is a Thunk? (from Gemini)
The following will make more sense after seeing an example. This explanation is from Google's AI: Gemini.
In Excel, especially with the introduction of LAMBDA functions and dynamic arrays, a thunk refers to a special case of a named LAMBDA function that takes no parameters but returns a predefined array or value when called with empty parentheses ().
Essentially, a thunk in Excel acts as a way to:
Delay evaluation (lazy evaluation): The calculation within the thunk isn't performed until the thunk is explicitly called. This can be useful for optimizing performance by avoiding computationally expensive operations until their results are actually needed.
Store complex data or functions: Excels dynamic arrays sometimes have limitations when dealing with arrays of arrays or direct references to ranges within certain functions (like MAP, REDUCE, SCAN). Thunks provide a workaround by allowing you to store a function that returns an array or range reference, rather than the array/range itself. You then unpack or release the thunk by calling it.
Pass by reference (conceptually): While Excel typically uses pass by value, thunks can emulate a pass by reference behavior. Instead of passing the actual data, you pass a function that, when executed, will retrieve or compute the data. This is particularly useful when you want to avoid duplicating data or when the data itself is a complex object like a range.
Key characteristics of a thunk in Excel:
LAMBDA without parameters: Its defined using LAMBDA() with no arguments.
Returns a value or array: When called, it yields the desired result.
Called with empty parentheses: To activate the thunk and get its value, you append () to its name or formula."
Here is what a typical Thunk looks like: = LAMBDA( x, LAMBDA( x))
If we enter that into a cell we get a #CALC! error that reports "Cell formula result returns a function". Cells must display values. They cannot display functions. We get the same error if we just enter: =SUM.
We might think that if we pass a value to this Thunk like this: =LAMBDA( x, LAMBDA( x))(10) it should return 10 but instead, we get another #CALC! error with the same "Cell formula result returns a function." In reality, that is returning, not 10, but LAMBDA(10) and that is a function, not a value.
To get our Thunk to return its value we must add an empty parenthesis like so: =LAMBDA( x, LAMBDA( x))(10)()
Easy right? Nope. At least not to me. I understand it how it works but not completely why it works. I know people who do know why it works, but knowing why isn't as important as knowing how. So now that we now HOW it works, let us see how we can use it.
NOTE!
To focus on the calculations, we will remove all lines related to inline help. We expect any function derived from this function to add customized inline help. For more on inline help see the 5g basic source structure template.
To help explain the Thunk and SCAN template we will convert a common corkscrew calculation; however, this is not intended to be a "corkscrew" template. A much faster and simpler, but less familiar-to-traditional-modelers corkscrew template is offered here.
Below shows a traditional corkscrew calculation. We will refer to this figure and focus on column J. All columns to the right of J are copies of column J's formulas and hold no particular interest for us.
Template
Below is BXL's 5g Thunk and SCAN template. It can be downloaded using AFE from this URL:
https://gist.github.com/CHatmaker/3e6cba6414fadfabfa3725255da2ba74
NOTE! The circled numbers are NOT part of the code. There are added to mark sections for discussion.
Function Name and Arguments.
All 5g functions require a name. The template is named "ScanAndStackλ." When creating your function, modify this name to suit your needs.
With rare exceptions, All 5g functions require arguments (aka parameters). The template has three arguments: Opening, Gains, and Losses.
Opening - This is the closing amount from the last period before this model's timeline (if any). It is a single number.
Gains - This is a row of values for each of the model's periods that increase the corkscrew's closing balance.
Losses - This is a row of values for each of the model's periods that decrease the corkscrew's closing balance.
Your function will require different arguments. Change these as needed to suit your needs.
Create THUNK function
This is what a thunk often looks like. This LET step merely declares an extremely simple function and names it: Thunk. It does nothing with it yet.
Load flows (rows) into Thunks array
We have two rows of values: Gains and Losses. We can stack them on top of each other and they use BYCOL to process each column, one by one, and place each column in its own Thunk function. The creates an array of Thunks, not an array of arrays. We name this array: Flows.
Calculate Close
This section creates the Close row using SCAN().
SCAN()
SCAN is a LAMBDA helper function that processes each element in an array, one by one. SCAN() has three arguments:
Initial Value - Many applications of the REDUCE and STACK template have an opening balance, in which case, place it here.
Array - The SCAN and STACK template process each Close value one at a time. We place our Flows array here for processing one at a time.
LAMBDA - SCAN's LAMBDA processes each column.
SCAN's LAMBDA
SCAN's LAMBDA has three arguments:
Acc - Acc is a mnemonic for "accumulator". The accumulator's value starts with SCAN's initial value which is when SCAN processes the array's first element. After processing the array's first element, SCAN places the result from the last LAMBDA calculation in its accumulator.
Period - SCAN() passes each array value, one at a time, to its LAMBDA. In this example our Flows array contains Thunks where each thunk contains a column of one Gain value and one Loss value.
Formula - The formula, as in many LAMBDA cases, is best broken into smaller formulas using LET().
Get current period values
SCAN sent a thunk from the Flows (plural) array to its LAMBDA. The LAMBDA names it Flow (singular). To get Flow to give up its array we place parentheses after its name like this: Flow(). Flow() is one column contain a Gain value (top) and a Loss value (below). We use INDEX to get each value.
Calculate close
In this LET step we add the accumulator to the current period's values.
Return result
The last line in a LET function returns SCAN's result.
Derive Open from Opening and Close
In this LET step we start with the Opening value and place the Close array, less Close's last value to its right.
Vertically stack rows together
In this LET step we place the Open row on top of the Gains, Losses, and Close rows.
Return our function's result
The last line in our LET() function returns our result.
Summary
Summary
This is one of those highly technical solutions that few people will understand. For that reason, I recommend using this only when performance is an issue. And even though reviewers may not understand 'how' this works, they can test it (which is superior to inspection) to know it works.
Do not use this for a simple corkscrew. This template adds value when we need to select current period values using INDEX. We do not need to do that for simple corkscrews.
Page updated
Google Sites
Report abuse