Good day!
I'm hoping that the answer to this is embarrassingly simple but for the life of me I cannot figure it out...
I'm trying to come up with a formula that would perform a calculation on a dynamic array but on a line-by-line basis, and hence it would return also a dynamic array with 1 column and as many rows as there in the source array.
For example - I've got a 4x5 dynamic array:
=RANDARRAY(4,5,0,10,TRUE)
then in F1 I would want to have a formula that returns a dynamic array with 1 column and 4 rows (because the 1st array has 4 rows) and each row in there would show the total from the 1st array's their corresponding row - meaning in:
F1 would show total of A1:E1, in
F2 would show total of A2:E2, in
F3 would show total A3:E3, etc.
The point is that I don't want to fill down F1:F4 mechanically - I want to have a formula that returns the dynamic array by itself.
Surely that can be done, can't it?
Hi Austris,
I'm not sure how you'd write a formula to do this, sorry. However, if you put your data in an Excel Table the formula e.g. =SUM(A1:E1) will automatically copy down as you add more data.
Mynda
In F1, that would be:
=BYROW(A1#,LAMBDA(a,SUM(a)))
A1 contains your RANDARRAY function.
Thanks Mynda - yeah, table could do it mechanically but without the spill unfortunately.
Thanks Riny - That's exactly the thing I'm after!! Thank you so much - (our IT haven't released the byrow/lambda just yet so I wasn't even looking for them but now this makes so much sense!)
Another option
=MMULT(A1#,SEQUENCE(COLUMNS(A1#),,,0))
I knew there were some really smart people here! Thanks!! (I wasn't sure if this should be posted as a different question - but it's directly linked to the starting one... - the same issue - getting spilled array via a row-by-row calc).
If I would push it up a notch - is it possible to construct a function without lambda/byrow to work like this would:
=BYROW(A1#,LAMBDA(a,INDEX(FILTER(a,a>2),1,1)))
i.e., so that I would get back a spilled array of the 1st integer in every row that is greater than 2?
For instance, if my 1st array is:
| 3 | 3 | 3 | 3 |
| 1 | 5 | 2 | 5 |
| 1 | 1 | 4 | 1 |
then my resulting spilled array would be:
3
5
4
