Add Corkscrew function for dynamic arrays
Corkscrew accounting is fundamental to Financial Modeling. It is where opening balances are carried over from closing balance of prior periods..
Where FirstPeriodBalance is the first period's opening balance and PriorBalances is an array of closing balances for each period.
The cell containing this function will return FirstPeriodBalance. The SPILL range will contain the n-1 closing balance array value.
James Denman commented
Agree, this is a standard case and should be supported by Dynamic Arrays, see attached file.
Craig Hatmaker commented
Sorry - I focused too much on the solution and not enough on problem. The problem is dynamic arrays currently cannot distinguish when an element in an array is not dependent on values in the same column that reference it. In this case, the opening balance is pulling a value from the prior closing balance column and not the current closing balance column. So when a closing balance cell adds the opening balance from the same column, it is NOT a circular reference.
Jeff Robson commented
While it's possible to simulate corkscrew functionality using Dynamic Array functions ( https://accessanalytic.com.au/corkscrew-calculations-using-dynamic-array-functions/ ), I agree that this is a pretty ugly and unnecessarily complex solution so would rather support your idea (or the ACCUMULATE function Peter mentioned below) and have these built into Excel.
There needs to be a simple solution to this problem and it is currently a significant omission that prevents dynamic arrays from being used as they should be in financial modelling.
Peter Bartholomew commented
I believe this addresses the most important roadblock to the adoption of dynamic array methods in many areas of modelling in which the natural mode of addressing data is by index (that is arrays, rather than lists and tables which may be sorted and records are addressed by key). The problem is that, in order to accumulate values each balance needs to reference the prior balance, something that dynamic arrays treat as a circular reference.
Craig knows that his proposed solution is not my preferred approach. I would prefer an ACCUMULATE function as implemented by Charles Williams in FastExcel.
At this point, though, I would back any solution that addresses the problem. It is that important.
Craig Hatmaker commented
Hi Kenneth Barber,
Thank you for your comments.
Your assumptions hold true for traditional Financial Modelers who structure models in chronological order (horizontally) with no duplicates. Dynamic Arrays hold great promise for Financial Modelers by making models:
1) Dynamic - by automatically accommodating any number of periods with no formula changes
2) Robust - by dramatically reducing formula cell count (failure points).
3) Efficient - by dramatically reducing distinct formula count.
Spilled arrays cannot be placed in tables making tables and dynamic arrays two totally different paths for modelers. Personally, I prefer tables because Structured References make formulas self documenting and more transparent (see https://arxiv.org/ftp/arxiv/papers/1802/1802.01628.pdf). For those wanting to learn about table based modeling see my site: https://sites.google.com/site/beyondexcel/project-updates/advancedexcelmodeling-introduction
Dynamic arrays could work for Financial Modelers if they had a "corkscrew" function that was simple. I believe this suggestion is the only path to adoption by traditional Financial Modelers. Please vote for it.
Kenneth Barber commented
An issue with introducing the function that you are proposing is that it seems to assume that all of the prior balances are sorted in chronological order already. It also assumes that there are no duplicated periods. For this kind of calculation, I would recommend using tables. The formulas will be identical on every row, but they will calculate cell-by-cell rather than as a single array, and you will be free to sort your data however you like.
In any case, I think I know what you are trying to achieve. Assuming that you have a column of dates to represent the start or end of the period, and if your data is not necessarily sorted by date, you can use this formula:
That is, find the previous date (maximum date less than the current date) and then find it in the date column and return the corresponding previous balance. If there is no previous date, we handle that separately with IFNA.
However, if I take your suggestion more literally and assume that you do not have a date column, you can use this formula if <PriorBalances> is arranged into a column:
ROWS counts the number of rows. SEQUENCE will generate an array from 0 to ROWS()-1. We use ^-1^-1 to force a #DIV/0! error on 0. N^-1^-1 is just a cleaner way to write 1/(1/N). INDEX fetches the value in an array given a position (the array of 0 to ROWS()-1 represents the "n-1" position). Finally, we use IFERROR to handle the 1st case.