Help with a tricky formula doing an average across products across a 2D matrix and 2 dynamic arrays...

[jmwbowen]

Hello,

I'm needing help with a formula that'll be easier to describe with a picture:

• GF5:GF13 isn't relevant to the formula needed, it's a dynamic array and used to create the two-dimensional matrix of numbers
• GH4:GK4 is a transposed dynamic array and could be as short as 1 column or as long as 6 columns (numbers 1-6)
• GH5:GK13 is a two-dimensional array that counts the number of records in a separate table where two columns match the criteria of the above-mentioned arrays

What I need is a dynamic array formula in GG5 that will:

1. Multiply each value in the 2D array by the corresponding column's number in row 4, and then sum those products per row
2. Average that result by dividing that sum by the sum of the values in the corresponding row in the 2D dynamic array

Example:
Row 9 should result in the value 1.6666666~

• =sum((7*1),(3*2),(1*3),(1*4)) / sum(7,3,1,1)

Thanks in advance!

 

[shift-del]

Hi

=MMULT($GH$5:$GK$13,TRANSPOSE($GH$4:$GK$4))/MMULT($GH$5:$GK$13,SEQUENCE(4)^0)

 

[jmwbowen]

You rock....this worked.

I had to change the fixed references to dynamic references. I couldn't figure out why you had the "^0" in there, and then remembered that it needed to multiply by 1, so "SEQUENCE (4) ^ 0" worked the same as "SEQUENCE (COLUMNS($GH$4#),,,0)".

Final formula came out to:

=MMULT($GH$5#,TRANSPOSE($GH$4#))/MMULT($GH$5#,SEQUENCE(COLUMNS($GH$4#),,,0))