Forum Discussion

PapaAustin's avatar
PapaAustin
Copper Contributor
Apr 30, 2021

Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

Hello Excel Community, I have a simple row of numbers with a range name called Numbers I'm looking for a spill formula (one that returns a dynamic array) that returns a cumulative sum.   I unders...
excel
office 365
PapaAustin's avatar
PapaAustin
Copper Contributor
Apr 30, 2021

Rajesh_Sinha 

Hello,

Thanks for looking at my post. But unfortunately, this formula does not spill.

Making it spillable is what makes this particular formula exercise tricky 😞

 

I'm not sure if there is a good way to do this without using Lambda (which I currently do not have an access for)

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 30, 2021

PapaAustin 

It is spill, please check attached. If you change spill in B2 formula follows it.

If you may do something without lamdas, do it. Lambdas in Excel are at very beginning, if you do something using them now, in few months most probably it could be done much easier. Also with lambdas. IMHO, using lambdas now is only to train the brains and be prepared to future Excel.

 

  • mlylyly's avatar
    mlylyly
    Copper Contributor
    Oct 21, 2022

    SergeiBaklan 

    This is a great solution!

    Sorry to ask additional questions in an old thread but is there a solution where the Dynamic Array (spill) is included in the cumulative sum formula?

     

    My spill formula is P4=INDEX(SORTBY(B4:C8,B4:B8,1),,2), where the cumulative formula is
    =LET(s, P4#, k, SEQUENCE(ROWS(s)), SUBTOTAL(9,OFFSET(s,0,0,k)))

    However, I would like to have a formula corresponding to 
    LET(s, INDEX(SORTBY(B4:C8,B4:B8,1),,2), k, SEQUENCE(ROWS(s)), SUBTOTAL(9,OFFSET(s,0,0,k)))

    This does not work because the OFFSET() does not reference a cell - is there a way to solve this?

    Thanks a lot!




      

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Oct 21, 2022

      mlylyly 

      You may use SCAN like

      =LET(s, array, SCAN(0, s, LAMBDA(a,v, a+v) ) )
      • mlylyly's avatar
        mlylyly
        Copper Contributor
        Oct 21, 2022

        SergeiBaklan 

         

        Appreciate the quick response! 

        I don’t have the SCAN() function, it is a corporate version so don’t think I will be able to update excel to get access? Is there another solution than SCAN()? 

        thanks!

  • PapaAustin's avatar
    PapaAustin
    Copper Contributor
    Apr 30, 2021

    SergeiBaklan 

    Hello Sergei,

    I was replying to another commenter (his solution was using Sum(index... :index) approach which does not spill.

     

    Yours look like it would spill as you pointed out. 

    I do not have an access to the latest O365 update with Let and Lambda features (I'm on an enterprise license and probably won't have those features until much later); I tried your file but it gives me #name error because my Excel doesn't know what Let() is.

     

    So far, i have found this approach; it is rather a lengthy formula but it works.

     

    =TRANSPOSE(MMULT(
    (COLUMN(MyRange) <= TRANSPOSE(COLUMN(MyRange)))*MyRange,
    TRANSPOSE(SIGN(MyRange))
    ))

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 30, 2021

      PapaAustin 

      LET() shall be in production, perhaps except deferred semi-annual channel, not sure. MMULT() works fine, but that's not exactly the spill.

      • keenadvice's avatar
        keenadvice
        Brass Contributor
        Nov 23, 2021

        I know this has been answered now
        But random googling on my part threw up this post
        This one definitely works (and is simpler than some others here)

         

         

Resources