Search code examples
excelmathexcel-formulaoffice365spill-range

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.

Solution

  • 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