+ Reply to Thread
Results 1 to 5 of 5

Formula - Sum and multiplying - Dynamic array spilled behavior

  1. #1
    Registered User
    Join Date
    11-30-2020
    Location
    Norway
    MS-Off Ver
    365
    Posts
    10

    Formula - Sum and multiplying - Dynamic array spilled behavior

    Hi all. I have a question about dynamic array. A workbook is attached. The product, volume, and price in "Calculation" are dynamic array linked to "Drivers". When a new row (new product) is added between product b and c in "Drivers", the formula (multiplying, sum) in "Calculation" are messed up. What is the proper way to set up the formula so they could move with the dynamic array? The desired output should look like the attached image. I hope my question is clear. Thank you for looking into this.
    Attached Images Attached Images
    Attached Files Attached Files

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

    Re: Formula - Sum and multiplying - Dynamic array spilled behavior

    Please try

    =LET(d,A1:C5,ro,SEQUENCE(ROWS(d)+1),c,SEQUENCE(,COLUMNS(d)+1),
    r,IFERROR(IF(c>COLUMNS(d),INDEX(d,,2)*INDEX(d,,3),d),"Revenue"),
    t,IF(c=1,"Total",IF(c=2,"",MMULT(SEQUENCE(,ROWS(r),,0),N(+r)))),IF(ro>ROWS(d),t,r))
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-30-2020
    Location
    Norway
    MS-Off Ver
    365
    Posts
    10

    Re: Formula - Sum and multiplying - Dynamic array spilled behavior

    Thank you very much Bo_Ry. I have one follow up question. Could you please also help look into the attached workbook? One additional sheet "Result" is added where I would like to list the total volume and revenue calculated in "Calculation". However once a row is added in "Drivers", the references in "Result" do not shift. The reference of volume and revenue should move to C7 and D7 in "Calculation". Is there a way to achieve this? Thank you.
    Attached Files Attached Files
    Last edited by ttch; 11-30-2020 at 02:30 PM.

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

    Re: Formula - Sum and multiplying - Dynamic array spilled behavior

    Please Try

    =LOOKUP(9^9,INDEX(Calculation!A1#,,3))

    and
    =LOOKUP(9^9,INDEX(Calculation!A1#,,4))
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-30-2020
    Location
    Norway
    MS-Off Ver
    365
    Posts
    10

    Re: Formula - Sum and multiplying - Dynamic array spilled behavior

    Quote Originally Posted by Bo_Ry View Post
    Please Try

    =LOOKUP(9^9,INDEX(Calculation!A1#,,3))

    and
    =LOOKUP(9^9,INDEX(Calculation!A1#,,4))
    This works well. Thank you very much.

+ 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. Countifs / Sumifs with dynamic arrays (spilled ranges)
    By esbencito in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-08-2020, 02:43 PM
  2. Dynamic, Spilled, Named Ranges - how to override SPILL
    By TimB in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-24-2020, 01:53 PM
  3. [SOLVED] YTD with dynamic spilled array
    By MatthewHart74 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-14-2019, 07:49 AM
  4. YTD with spilled dynamic array
    By MatthewHart74 in forum Office 365
    Replies: 0
    Last Post: 12-12-2019, 12:05 PM
  5. [SOLVED] Behavior of Offset function in an array formula
    By mohd9876 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-05-2012, 07:40 AM
  6. PERCENTRANK in array formula: strange behavior
    By vezerid in forum Excel General
    Replies: 4
    Last Post: 03-09-2006, 12:11 PM
  7. Replies: 2
    Last Post: 02-21-2006, 08:00 AM

Tags for this Thread

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