# Formula - Sum and multiplying - Dynamic array spilled behavior

1. ## 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.  Register To Reply

2. ## Re: Formula - Sum and multiplying - Dynamic array spilled behavior

=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))  Register To Reply

3. ## 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.  Register To Reply

4. ## Re: Formula - Sum and multiplying - Dynamic array spilled behavior

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

and
=LOOKUP(9^9,INDEX(Calculation!A1#,,4))  Register To Reply

5. ## Re: Formula - Sum and multiplying - Dynamic array spilled behavior Originally Posted by Bo_Ry =LOOKUP(9^9,INDEX(Calculation!A1#,,3))

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

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