+ Reply to Thread
Results 1 to 3 of 3

Dynamic Array Cumulative Sum

  1. #1
    Registered User
    Join Date
    04-22-2020
    Location
    Australia
    MS-Off Ver
    O365
    Posts
    4

    Dynamic Array Cumulative Sum

    Hi All,

    I need help with a cumulative sum using the new dynamic array functions of O365.

    Attached is a sample sheet outling the problem.

    I wish to add No of members as at the model start date to the cumulative number of member renewals each month in the array G8# in the array G16#.

    The desired results are highlighted in green at the bottom.

    The formula currently in G16 works only for the cumulative sum vertically of E16# and I can't seem to adapt it to work horizontally.

    I would appreciate some help.

    Thanks

    Ron
    Last edited by moglij; 04-27-2020 at 05:12 AM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,096

    Re: Dynamic Array Cumulative Sum

    Please try

    =E22#+SUBTOTAL(9,OFFSET(G8,ROW(G8:G11)-ROW(G8),,,G4#))

    or non volatile

    =E22#+TRANSPOSE(MMULT(--(SEQUENCE(COLUMNS(G4#))>=SEQUENCE(1,COLUMNS(G4#))),TRANSPOSE(G8#)))

    you may replace G8# with
    SEQUENCE(ROWS(tblMemProd[Membership Product]),COLUMNS(G4#),1000,100)

    =E22#+TRANSPOSE(MMULT(--(SEQUENCE(COLUMNS(G4#))>=SEQUENCE(1,COLUMNS(G4#))),TRANSPOSE(SEQUENCE(ROWS(tblMemProd[Membership Product]),COLUMNS(G4#),1000,100))))
    Last edited by Bo_Ry; 04-27-2020 at 04:36 AM.

  3. #3
    Registered User
    Join Date
    04-22-2020
    Location
    Australia
    MS-Off Ver
    O365
    Posts
    4

    Re: Dynamic Array Cumulative Sum

    Hi Bo_Ry,

    Fantastic! Thanks so much.
    Works perfectly.
    I think we are going to be friends. Or more probably like neighbours where I am the annoying one who's always borrowing tools.

    Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Ascendent array cumulative
    By Yaghoub61 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-09-2020, 01:25 PM
  2. [SOLVED] Dynamic table cumulative totals.
    By eman22712 in forum Excel General
    Replies: 4
    Last Post: 02-04-2020, 08:04 AM
  3. Dynamic Cumulative in a Range
    By excelnewbie2407 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-10-2017, 11:09 AM
  4. Self changing dynamic cumulative total
    By vinzo1309 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-20-2016, 12:23 PM
  5. Dynamic cumulative sum formula (?!)
    By WineFerret in forum Excel General
    Replies: 4
    Last Post: 05-13-2016, 07:49 AM
  6. [SOLVED] Dynamic cumulative SUM RANGE - different months
    By epsiloni in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-15-2013, 11:36 AM
  7. Cumulative Sum of values in an array
    By seigna in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2013, 06:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1