Forum Discussion
Product of numbers in a row in a dynamic array
How does one do row-by-row evaluations with a 2d dynamic array?
For example, I need the product of all the numbers in each row in an array. I can only seem to find ways to get the product of all the numbers in all rows of the array, reduced to a single number.
My spilled array:
0 2 2
2 1 1
3 3 2
Result sought:
0
2
18
from
0*2*2
2*1*1
3*3*2
I've struggled mightily and I'm about to resort to some very ugly text manipulation plus a name with evaluate based on the fact that I know my numbers will all be single digits 😞 I really do not want to do this!
=LET(
rows, ROW(array)-ROW(INDEX(array,1,1)),
width, COLUMNS(array),
astext, TEXTJOIN("*",,array)&"*",
MID(astext,rows*width*2+1,width*2-1)
)
It seems absurd that I can make four rows of text, but not fours rows of results.
Thanks for any insights.
This is something I have been complaining about for a couple of years or so now but, as JKPieterse says, the definitive solution is to be found in the Insiders beta Channel. BYROW will return each row as a distinct range reference and supports any relevant calculation.
As an interim workaround, the multiplication of positive numbers may be performed by using logarithms
= EXP( MMULT(LN(array#), {1;1;1}) )
21 Replies
boukasa A Secong solution could be to get each dynamic columns using Index, and multiply them:
=INDEX(H4#;SEQUENCE(ROWS(H4#));1)*INDEX(H4#;SEQUENCE(ROWS(H4#));2)*INDEX(H4#;SEQUENCE(ROWS(H4#));3)
/Geir
- Geir, thanks so much - my problem includes a dynamic number of columns. I don't fully grasp yet how Excel "views" an array, but I was surprised that you don't seem to be able to use an array for the row/column parameters of INDEX.
I am not sure what you have in mind for this reply to Hogstad_Raadgivning. INDEX will accept array and lift the appropriate scalar parameters, For example
= INDEX(array#, {2;5;3}, {4,2,5})would assemble a 3x3 array from a larger array. It would still have the same problem as the original,
i.e. functions like PRODUCT would still aggregate the entire array, giving a single value. For row products, you would still need
= BYROW( INDEX(array#, {2;5;3}, {4,2,5}), LAMBDA(row, PRODUCT(row)) )I am just puzzled, not critical.
This is something I have been complaining about for a couple of years or so now but, as JKPieterse says, the definitive solution is to be found in the Insiders beta Channel. BYROW will return each row as a distinct range reference and supports any relevant calculation.
As an interim workaround, the multiplication of positive numbers may be performed by using logarithms
= EXP( MMULT(LN(array#), {1;1;1}) )Thanks so much for this workaround! I don't have access to the BYROW function yet, but your solution definitely works for my use case for now. My column count is dynamic so I've made your formula:
=EXP(MMULT(LN(array#),TRANSPOSE(SEQUENCE(1,COLUMNS(array#),1,0))))
Much appreciated!
- You probably need the new BYROW function: https://support.microsoft.com/en-us/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb
