Return of the Corkscrew with Spilled Arrays
I’m starting a new topic but do want to refer to @PeterBartholomew1 post on accumulators here
This post was in 2021 and things have or may have moved on since then and I’m wondering if there is an easier way to achieve the following.
The Corkscrew. I have seen a number of addins and other 5G functions which do far more than I want. I’m basically looking for a way to break the circularity in a corkscrew and it seems that if I can calculate the top row ie typically the opening balance without referring to the closing balance then all would be good.
To add complexity I’m trying to fuse the actuals and forecasts in this single function. I have flags to determine the actuals and can use NOT actuals to get the forecasts (same flags) and can pic up the actual opening balance from the source data easily. As I will calculate all time periods for both I can then just add them (as actuals will be multiplied by the Actual flag and thus zero for forecast periods and vice versa for the forecasts. So I just need to calculate the forecast period which will be the same for the entire array block. The key is. getting the previous value. (for the inflows and outflows as they are already calculated I can just shift their arrays forward I col but adding a column in front to get the previous value so that is easy.
I can then get the closing balance by adding the inflows and outflows that would fall in the middle of the corkscrew to this previous value to simulate an opening balance. The inflows and outflows can then be added easily and the closing balance simply summed. So all the clever work would go in the opening balance.
The added complexity I have is that I am doing a multi row version. ie there are ‘currently’ 3 entities all with opening balances, all with inflows and outflows and closing balances. I want to calculate these in blocks of these entities – 1 row per entity but in a single dynamic array. But the corkscrew will be made up of 4 dynamic arrays – opening bal, inflows, outflows and closing.
If someone has a better logic here then do let me know. I saw Jeff Robsons one from a few years back using sumifs for the middle bit. But again, I want to do these in separate blocks which does add a layer of complexity. The reason is that I can ( have) modelled with multiple entities together rather than separately which means one just needs to extend the blocks (VBA to the rescue as there will be a good number of them) as required and inputs can just be assigned to the respective entity (often locations around the world might just be a tiny rep office with hardly anything going on so no need for a fully blown model but can be detailed separately. I also have the ability to enable or disable entities so they are included or excluded from the calcs.
SO back to the problem, Model attached. Blue cells mark the dynamic array function for the block. The inflows and outflows will come from other dynamic arrays. But this idea means that one can expand the middle if the top/bottom are taken care of with regard to the circularity.
I’m trying to get something easy to implement ideally in a single function (with or without LET)
I’m starting a new topic but do want to refer to @PeterBartholomew1 post on accumulators herehttps://techcommunity.microsoft.com/t5/excel/ways-of-performing-accumulation-with-dynamic-arrays/m-p/2329035This post was in 2021 and things have or may have moved on since then and I’m wondering if there is an easier way to achieve the following.The Corkscrew. I have seen a number of addins and other 5G functions which do far more than I want. I’m basically looking for a way to break the circularity in a corkscrew and it seems that if I can calculate the top row ie typically the opening balance without referring to the closing balance then all would be good. To add complexity I’m trying to fuse the actuals and forecasts in this single function. I have flags to determine the actuals and can use NOT actuals to get the forecasts (same flags) and can pic up the actual opening balance from the source data easily. As I will calculate all time periods for both I can then just add them (as actuals will be multiplied by the Actual flag and thus zero for forecast periods and vice versa for the forecasts. So I just need to calculate the forecast period which will be the same for the entire array block. The key is. getting the previous value. (for the inflows and outflows as they are already calculated I can just shift their arrays forward I col but adding a column in front to get the previous value so that is easy. I can then get the closing balance by adding the inflows and outflows that would fall in the middle of the corkscrew to this previous value to simulate an opening balance. The inflows and outflows can then be added easily and the closing balance simply summed. So all the clever work would go in the opening balance. The added complexity I have is that I am doing a multi row version. ie there are ‘currently’ 3 entities all with opening balances, all with inflows and outflows and closing balances. I want to calculate these in blocks of these entities – 1 row per entity but in a single dynamic array. But the corkscrew will be made up of 4 dynamic arrays – opening bal, inflows, outflows and closing. If someone has a better logic here then do let me know. I saw Jeff Robsons one from a few years back using sumifs for the middle bit. But again, I want to do these in separate blocks which does add a layer of complexity. The reason is that I can ( have) modelled with multiple entities together rather than separately which means one just needs to extend the blocks (VBA to the rescue as there will be a good number of them) as required and inputs can just be assigned to the respective entity (often locations around the world might just be a tiny rep office with hardly anything going on so no need for a fully blown model but can be detailed separately. I also have the ability to enable or disable entities so they are included or excluded from the calcs. SO back to the problem, Model attached. Blue cells mark the dynamic array function for the block. The inflows and outflows will come from other dynamic arrays. But this idea means that one can expand the middle if the top/bottom are taken care of with regard to the circularity. I’m trying to get something easy to implement ideally in a single function (with or without LET) Read More
