Forum Discussion

boukasa's avatar
boukasa
Brass Contributor
Oct 12, 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})
  )

     

Hogstad_Raadgivning's avatar
Hogstad_Raadgivning
Iron Contributor
Oct 12, 2021

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

  • 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.

      • boukasa's avatar
        boukasa
        Brass Contributor
        Oct 12, 2021

        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.

Resources