0

I have 2 dynamic 2d arrays of equal length n x y. I want to add every entry of row n of the 1st array to every entry of row n of the 2nd array for every row. It will make a n x n spilled 2d array for each row. Based on some criteria I want to filter out 1 value out of it at a later stage. I have made a formula that can do that but has no spill behavior. I want it to be dynamic and expand to n rows automatically without dragging it.

Image of 2 arrays and addition formula is attached

I know the reason it is not dynamic is because of the row_num entry of the index formula. I tried to use sequence formula but it messed up the addition. Any non-VBA solution to this?

Edit:

I want this output in U472 The formula used in U47 is

=TRANSPOSE(INDEX($M$47#,ROW(M47)-ROW($M$47)+1,) + TRANSPOSE(INDEX($Q$47#,ROW(M47)-ROW($M$47)+1,)))

I want this output in U483

In my case, I can't have this because I will have a spill error. I know I can't have a 4 x 4 output in one cell because it will spill if I have a value in a cell below it.4

However, I will only use one value out of the 16 values depending on certain criteria. One thing I can do is to apply the criteria in the cell like this 5

The problem is that the array doesn't remain dynamic. I have to drag it every time the number of rows of array M47# and Q47# changes. I want it to be dynamic. What can I do to encounter this problem?

Improve this question
3
  • Addition will result in a y x y 2d array not n x n 2d array
    – Osama Anwar
    Commented Apr 18 at 14:22
  • =TRANSPOSE(INDEX($M$47#,ROW(M47)-ROW($M$47)+1,) + TRANSPOSE(INDEX($Q$47#,ROW(M47)-ROW($M$47)+1,))) formula in the cell
    – Osama Anwar
    Commented Apr 18 at 14:29
  • 1
    Side note: it's better to include your formula as text and not as a screencapture, that way people who want to test it out don't need to retype it all.
    – cybernetic.nomad
    Commented Apr 18 at 14:56

1 Answer 1

Reset to default
0

Not sure whether you are trying to accomplish something like this or not:

enter image description here

• Formula used in cell U47

=WRAPROWS(TOCOL(TOROW(Q47#)+TOCOL(M47#)),4)

Edit: Updated formula:

enter image description here

=DROP(REDUCE("",SEQUENCE(ROWS(M47:T49)),LAMBDA(α,δ,
 LET(φ,TOCOL(INDEX(M47#,δ,))+INDEX(Q47#,δ,),
 VSTACK(α,IFNA(MIN(φ),""))))),1)
Improve this answer
22
  • No not like that. I want to add 1st row entries of Array M47# i.e. 0.8,1.2,1.8,3.2 to 1st row entries of Array Q47# i.e. 0,0,0,0 it will give out a 4x4 2d array in U47 and same for second row and so on. I have already done this. I want a formula to do this for every row and spit the output in same row i.e. for 2nd row I want my output in U48. I know that 4x4 output can't reside in 4x1 space. So suppose I want one value out of 4x4 based on a given criteria. Let criteria be min value. So formula should spit values in adjacent rows and be dynamic. My formula does not remain dynamic.
    – Osama Anwar
    Commented Apr 19 at 2:28
  • By addition I mean each entry of M47# been added to each entry of Q47#. But not at once. One row at a time. Your solution adds each entry of whole array to each entry of other array. What I want is to add 1st row entries of both arrays then 2nd row entries of both array. Hope it is more clear now.
    – Osama Anwar
    Commented Apr 19 at 2:36
  • Addition will spit out 4x4 for each row. 4x4 for U47 and 4x4 for U48. And there is no need to drag the formula. It should be dynamic in nature.
    – Osama Anwar
    Commented Apr 19 at 2:39
  • Thanks for the feedback. I have edited my post.
    – Osama Anwar
    Commented Apr 19 at 6:05
  • Yes blue ones are my output. But in last image output have no color.
    – Osama Anwar
    Commented Apr 19 at 6:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .