Notifications
Clear all
Topic starter
Hello all 🙂
If I am not making any sense, I apologize lol
I have a spilled range (B1:D2)
| Plan | -1 | -1 | -1 |
| Actual | 1 | 0 | 2 |
| Desired result | 0 | -1 | 0 |
Right now, the desired result formulas are
| Plan | -1 | -1 | -1 |
| Actual | 1 | 0 | 2 |
| Desired result | =SUM($B$1:B$2) | =SUM($B$1:C$2) | =SUM($B$1:D$2) |
But my spilled range is dynamic and can grow or expand. So I am hoping this is possible for a spilled range formula that will expand with it.
Thanks in advance!
Posted : 01/12/2023 3:40 pm
Try this one:
=SCAN(0,BYCOL(B1#,LAMBDA(x, SUM(x))),LAMBDA(a,v,a+v))
Broken down is two steps:
BYCOL( B1#,LAMBDA( x, SUM(x) ) )
this will create a dynamic array with the sum of each column { 0, -1, 1 }
SCAN( 0, { 0, -1, 1 }, LAMBDA(a, v, a+v) )
produces the running total for each column { 0, -1, 0 } , again as a dynamic array.
Posted : 02/12/2023 2:45 am
Just as an alternative:
=BYCOL(B1#,LAMBDA(c,SUM(B1:INDEX(c,2))))
Posted : 02/12/2023 4:26 am
