CODEWITHSUNDEEP
excelexcel-formula
Fabricio Antonello
Fabricio Antonello

Reputation: 53

Spill formula on % calculation

I need to calculate % increase for a series of values but I need to do it in 1 column instead of 3 or 4, but I am having mental blockage after trying different things.

On the example, the GREEN column is the reult I need on the spill cell and SPAN formula was the one which got me closest but from the 2nd row onwards it does add the calculation before returning which I don't know how to resolve. Tried multiple tricks but only got me even farther from the result I need.

File Available Hereenter image description here

I think this is potentially a very easy solution but my mind is frying and after 2 days on it I am getting nowhere so I appreciate any tip you could share.

Upvotes: 1

Views: 113

Answers (4)

Black cat
Black cat

Reputation: 6107

With the traditional functions

Formula in cell B10:=(SUM($A$10:A10)+SUM($B$9:B9))*$A$8

enter image description here

Upvotes: 0

rotabor
rotabor

Reputation: 4493

Evidently, the solution becomes simpler after some mathematical processing:

D7 =SCAN(0,C7:C13,LAMBDA(acc,itm,acc+(acc+itm)*A1))

enter image description here

Upvotes: 0

nkalvi
nkalvi

Reputation: 2614

REDUCing, just for fun,

=LET(
    incr_percent, $A$1,
    increments, C7:C13,
    calc, 0,
    next_start, 0,
    REDUCE(
        HSTACK(calc, next_start),
        increments,
        LAMBDA(acc, incr,
            LET(
                start, TAKE(acc, -1, -1),
                calc, (start + incr) * incr_percent,
                next_start, start + incr + calc,
                VSTACK(acc, HSTACK(calc, next_start))
            )
        )
    )
)

Result

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152450

You will need to then do the steps of all the columns in one formula. We can use LET( to simplify:

=LET(
    _r,C6:C13,
    _rt,A1,
    _ns,SCAN(0,_r,LAMBDA(_i,_x,(_i+_x)*(1+$A$1))),
        _ns -
        (_r +
        DROP(VSTACK(0,_ns),-1))
    )

As you can see we take the result of the scan that returns the Next start and subtract the sum of that same array shifted down one space and the values in the range.

enter image description here

Upvotes: 3

Related Questions