Forum Discussion

boukasa's avatar
boukasa
Brass Contributor
Oct 11, 2021
Solved

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 ...
excel
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Oct 12, 2021

    boukasa 

    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's avatar
boukasa
Brass Contributor
Oct 12, 2021
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.
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 12, 2021

boukasa 

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.

Resources