REDUCE and VSTACK the accumulator

[saboh12617]

Hello,

My problem is the following:

I have values in dynamic arrays, let's say in A1#, B1#, C1#. (The arrays have same length). Let's call the value of each array respectively a, b, c.

I want to compute, for each row an operation. For instance 2*a+b+c, and extract only the rows (ie. Lines of {a_i \ b_i \ c_i}) for which 2*a+b+c = 120.

I think REDUCE is the function i should use. However i can not manage to make it work properly as the VSTACK on my accumulator is messed up.

For now I am using a filter but i wonder if there is a "cleaner way" :

=LET(myArr;HSTACK(A1#;A2#;A3#);
FILTER(myArr;BYROW(myArr; LAMBDA(r; CHOOSECOLS(r;1)*2+CHOOSECOLS(r;2)+CHOOSECOLS(r;3)=120))))

 

[hagia_sofia]

Hello, you could e.g. use MAP:

=LET(myArr,HSTACK(A1#,B1#,C1#),
FILTER(myArr,MAP(CHOOSECOLS(myArr,1),CHOOSECOLS(myArr,2),CHOOSECOLS(myArr,3),LAMBDA(a,b,c,(a*2+b+c)=120))))

or

=LET(
myArr,MAP(A1#,B1#,C1#,LAMBDA(a,b,c,2*a+b+c)),
FILTER(HSTACK(A1#,B1#,C1#),myArr=120))

 

[djclements]

FYI, basic formulas that use calculation operators (arithmetic, comparison or text concatenation) do not require helper functions like BYROW, MAP, etc. They will spill naturally via broadcasting and apply the calculation on an element-by-element basis. For example, 2*A1# will multiply 2 to every element in range A1#. BYROW, MAP and the like are only really needed when using aggregate functions (e.g. SUM or PRODUCT).

Also, three adjacent spill ranges of the same length do not require HSTACK to merge into a single range reference. You can simply use the range operator (colon).

The sample formula provided could be simplified as follows:

=FILTER(A1#:C1;(A1#*2+B1#+C1#)=120)

 

[felixstraube]

Another alternative:

=LET(a,A1#,b,B1#,c,C1#,
FILTER(HSTACK(a,b,c),2*a+b+c=120))

 

[saboh12617]

Hello everybody,

Thank you very much for all your answers, they are exactly what i was looking for. I'm new to those spill arrays and their behaviour and i learnt a lot. I've got my problem solved. The last answer is probably the one i will be using in this specific case since my parameters (columns A,B,C) will already be "named arrays" in a bigger LET.

The A1#:C1 is neat tho, never seen before. Also thanks for the clarification on the BYROW usage, i had some concerns on when to use it and when it was possible to avoid it.

Have a good day!

 

[felixstraube]

Glad we could help. Thanks for the feedback.