Running sum in the same cell

  Kiến thức lập trình

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.

  • excel-formula

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)
)

Theme wordpress giá rẻ Theme wordpress giá rẻ Thiết kế website

LEAVE A COMMENT