We think this user is asking the wrong question. The instinct to build a single, all-encompassing formula is understandable, it feels elegant, efficient, and future-proof. But when you try to force `MAKEARRAY` to behave like a running total calculator, you are fighting the tool instead of letting it work for you. The core issue here isn't that Excel lacks the capability; it's that the problem has a simpler, more reliable solution hiding in plain sight.
What this user actually needs is a helper column approach that separates concerns. Start with `SCAN` for each row individually, then use `BYROW` to apply it across the dataset. The trick is to structure your data so that `SCAN` receives only one row at a time. A helper column that concatenates the starting inventory with the daily values, or a `LAMBDA` that wraps `SCAN` inside `BYROW` correctly, eliminates the nested array error. The formula `=BYROW(H2:N8, LAMBDA(row, SCAN(INDEX(row,1), DROP(row,,1), LAMBDA(a,b, a+b))))` works because `SCAN` now operates on a single row's array, not a 2D block. This is not a workaround, it is the intended design pattern for modern Excel.
For the user who wants everything in one cell without helpers, we respect the ambition. But practical productivity means choosing the approach that degrades gracefully when you revisit the spreadsheet six months later. A single `MAKEARRAY` formula with nested `SUM` ranges is brittle; if a row shifts or a column gets inserted, the logic breaks silently. The helper column, by contrast, is self-documenting. Each step is visible, testable, and fixable. That matters more than the aesthetic of a single formula.
So here is our plain opinion: stop hunting for the one formula to rule them all. Build your running total with `BYROW` and `SCAN`, using a helper column if needed. You will get the exact same result, dynamic, spill-friendly, and row-independent, without the `#CALC!` errors. The best solution is the one you can trust when you open the file a year from now. That is not the cleverest formula. It is the clearest one.