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
Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Apr 30, 2021

PapaAustin 

 

You may try this :

 

 

=SUM(INDEX(Mydata,1,1):INDEX(Mydata,ROW()-ROW(INDEX(Mydata,1,1))+1))

 

 

 

  • Formula in cell S86, fill it down:

 

 

N.B. Replace Mydata (is Named Range) with the NAME you have given to the RANGE.

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)

  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    May 01, 2021
    Please share some sample data along with the expected output,,, otherwise what I've suggested & by SergeiBaklan are working for Cumulative Sum as you have mentioned with the post !!
  • 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) ) )
    • 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))
      ))

Resources