Log In
Register
Home
July 13, 2021
Offline
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?
See Answer
July 16, 2010
Offline
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
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
In F1, that would be:
=BYROW(A1#,LAMBDA(a,SUM(a)))
A1 contains your RANDARRAY function.
Answers Post
July 13, 2021
Offline
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!)
1 Guest(s)
