Forum Discussion
Dynamic Arrays - Extend the last logical row value in a 2D array
One more for experts. Again, with my new found knowledge I still was not quite able to make this work.
Simple balance sheet values that I need to extend into the forecasts as is. i.e. the last actual value in the row of that block - BUT, the block has several rows each for a different entity and I have an option for have staggered actuals based on the data loaded. i.e. if Entity A has values to 30th Sep 2024 then this is the latest value to carry forward for Entity A. But entity B may only have actuals up to 31st August so I need to take the value for August to carry forward.
I prefer to use flags for the logic. Sample attached
Appreciate the help
As always a fabulous solution - actually 2. I'm using the Index as it is actually so fundamentally simple. Not sure why I couldn't get there but added more to my war chest. I now fully get the sequence inside the index to force the rows. And, of course, xmatch in reverse!! Why did I not think of that!!
ANd the error handling then if flags are missing. Very nice and very reusable.
I now get the scan & map combo. I see the use of the new enhanced byrow - I will carry on with the older version for a bit longer until all have it rolled out but it took a few seconds to figure out how it worked as never actually just seen it in use!
So now I fully get it. The map lets you do the logical test with the flag to leave only the last values in the resulting array and then you just do the usual scan function on that using the NAs to reset the rows. That will be a great addition to my library and I can now think of a number of places I could use that kind of combination
Both fab. I may doa performance test sometime to compare them but the usage is not high so won't make much difference. I have 7 sets of these so far and don't notice anything on the calc speed.
Many thanks to you again - and Peter for his input.
A couple of possibilities include:
=LET( lastCols, BYROW(G6:N8,LAMBDA(r,XMATCH(1,r,,-1))), lastVals, INDEX(G12:N14,SEQUENCE(ROWS(G12:N14)),lastCols), IF(G3:N3<=INDEX(G3:N3,,lastCols),G12:N14,lastVals) )-OR-
=LET( isFcst, BYROW(G6:N8*G3:N3,MAX)<G3:N3, SCAN(0,MAP(isFcst,G12:N14,LAMBDA(f,v,LAMBDA(a,IF(f,a,v)))),LAMBDA(a,λ,λ(a))) )However, if any rows in the "flag" array don't contain a flag, additional steps would need to be taken, e.g. using IFNA to handle errors, which I've included in the attached file...
As always a fabulous solution - actually 2. I'm using the Index as it is actually so fundamentally simple. Not sure why I couldn't get there but added more to my war chest. I now fully get the sequence inside the index to force the rows. And, of course, xmatch in reverse!! Why did I not think of that!!
ANd the error handling then if flags are missing. Very nice and very reusable.
I now get the scan & map combo. I see the use of the new enhanced byrow - I will carry on with the older version for a bit longer until all have it rolled out but it took a few seconds to figure out how it worked as never actually just seen it in use!
So now I fully get it. The map lets you do the logical test with the flag to leave only the last values in the resulting array and then you just do the usual scan function on that using the NAs to reset the rows. That will be a great addition to my library and I can now think of a number of places I could use that kind of combination
Both fab. I may doa performance test sometime to compare them but the usage is not high so won't make much difference. I have 7 sets of these so far and don't notice anything on the calc speed.
Many thanks to you again - and Peter for his input.The new so called "platform upgrade" has broken this forum for me. It doesn't work properly on my cell phone (probably because it's too old/small), and I'm not about to buy a new phone just to keep up with a hobby, nor am I going to keep my laptop running all day just to monitor recent activity. The new threaded conversation view for replies/comments is also horrendous... nobody in their right mind would want to chase down every thread by clicking "Show More" over and over again to find the latest comment or to see all previous replies/suggested solutions. I don't think I'll be hanging out here much anymore. If you need any more help with your current or future projects, please feel free to email me direct. Take care.
I think the actuals should be held separately from any calculation. What you can do then is start the forecast at the first actuals year. If the actual holds a value, it may be reproduced by formula on the forecast sheet. I don't think the 'final actual' flags help that much, especially if they do not correspond to the table of actuals itself.
For example, a forecast with 0.5% monthly growth assumptions might look like
= SCAN(0, actuals, LAMBDA(acc,act, IF(act<>"", act, acc*(1+growth))))To handle multiple line items with one formula is where Microsoft have made things unnecessarily difficult.
If you want a chat sometime, that should be possible.
- Would love to chat some time.
Regarding this, actuals are pulled by formula as are the forecasts. But is thins case for small BS items, there will be no computation in the forecasts as they are small and so will just continue as per the latest actual. So what I want to do here is just pick up the last value in the actuals (it's a formula and the flags tell which period) and extend. But as each entity could have a different end date for actuals, I can't just take the block and drop or take either end then continue as the block end will not be straight. This accommodates when different subsidiaries have their accounts ready at different times. It enables a blending of actuals and forecasts based on actual data received.
I have a switch with 3 different option, fixed date, earliest actuals or staggered and this option is needed in the case of staggered actauls
I never mix actual hardcoded values with forecasts and all formula must start in the same column whether actuals or forecasts - hence the flags- I tried an option with scan but if there is a value in the actuals and then an empty or zero value before the actual last month of actuals, it then picked up that. Clearly if the last month of actuals is 0 then the amount carried forward should be zero. I couldn't see a way to avoid this using scan
