Forum Discussion
Product of numbers in a row in a dynamic array
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}) )
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
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.
Peter, I really appreciate you taking the time to look at this. I may have expressed my observation incorrectly or inartfully. Here's an example of what I mean:
In A1, put =SEQUENCE(5,5)
In A7, put =ROW(A1#)-ROW(A1)+1
In B7, put =INDEX(A1#,A7#,0)
In H7, put =INDEX(A1#,A7,0)
I don't understand why B7 doesn't produce full rows (array as parameter to INDEX) but H7 does (non-array as parameter to INDEX). I expected B7 to produce a row, and I also expected to be able to write PRODUCT(INDEX(A1#,A7#,0)) to get my row product - but that not only doesn't work, it reduces to a single result for the whole A1# range.What you get from Microsoft is that Excel never has handled 'arrays of arrays' or 'arrays of ranges' and they need to maintain backward compatibility. Now, however, it has become important because, with dynamic arrays, one keeps hitting the problem. Your formula
=INDEX(A1#,A7,0)
returns a range object; by that, COLUMN would return sheet column numbers and ISREF would return TRUE. As soon as you add the # to A7, Excel recognises that you want an array of ranges and blocks the calculation, instead returning an array of single values (it is not a range).
As for
=PRODUCT(INDEX(A1#,A7#,0))
that just suggests that Excel knew perfectly well what you wanted all along, but refused to give it to you!
Even with the helper functions MAKEARRAY etc., it can be difficult to return arrays of arrays.