Forum Discussion
Your thoughts serializing arrays to manage state in excel (by way of inventory tracking)
I really need to see the formula in the context of its workbook. You are doing a number of things that I wouldn't do but that does not mean they are wrong; they are worth exploring. I think you are calculating running totals for a number of products. The calculations I have experience of are simpler in that only one product stream is considered at a time but more complex in that it matches outputs to inputs on a FIFO basis.
I normally move outside the table to perform such calculation, using arrays and spill ranges rather than using the inherent replication of table formula. There seems to be some benefit of your approach in that persistent storage is used to hold the prior step of an cumulative calculation. The downside would appear to be that the serialised data representing the evolving state would seem to be cumbersome, so if appears there are swings and roundabouts to be evaluated.
I would suggest you use your serialised data to hold the current state but to record the minimum of accumulated data as possible. Since I would be using an array calculation, I would have access to every step returned by the calculation but that means that the entire calculation is processed each time data is added. I am sorry that my ability to visualise and evaluate your proposal has proved to be somewhat limited.