I need to compare a value with the sum of an expanding range.
I have this formula here:
=CHOOSE({1,2,3},SUM(D4),SUM(D4:D5),SUM(D4:D6))
Wondering if there is a way to get an array of sums in the same cell without writing out every sum with its own range but make it more compact. I’ve tried INDIRECT(“D4:D”&{4,5,6}) but it would only return the first value.
Thanks.
11
Is this what you’re trying to get (required Microsoft 365):
=LET(
d, D4:D6,
running_sum, LAMBDA(r,
ARRAYTOTEXT(MAP(SEQUENCE(r), LAMBDA(i, SUM(TAKE(d, i)))))
),
MAP(SEQUENCE(ROWS(d)), running_sum)
)