Excel formula, calculate product of values in each column of dynamic array-CodePudding

I'm working on a formula for a "result" column in a table that looks up data in another table based on some criteria in the main one, then displays it. Of the point where I'm getting stumped at, it is returning a dynamic 2D array (row returns are variable dependent on an FILTER/MATCH lookup). Example (4 columns x 3 rows) return value: {1,2,3,4;1,2,3,4;1,2,3,4}

I want to get the product of the values in each column such that the output from the formula using the example return value above would be this: {1,8,27,64}

(Which is the equivalent of doing this: {1,2,3,4} * {1,2,3,4} * {1,2,3,4})

How do I accomplish this using only formula functions?

All of my google searches have been coming back with methods that assume static table size, multiply the columns against each other, and/or the table "existing" within a sheet somewhere (and still don't give the result I'm looking for).

I've already figured this out via VBA, but due to the nature of how I'm using the sheet, waiting for the updates to run via VBA are too slow. It's not that it takes forever - it's only about 3-4 seconds for ~60 rows - but rather that I make a change to my lookup table, wait for the update to the result column in the main table, check the results, make another change, rinse & repeat 50 some odd times while I fine tune things to look the way I want them to, and the delays are wearing on me.

I'm also unsure that I can use helper rows/columns as the results would need to be packed too close together and would just display #SPILL, which results in the formulas referencing it just showing the same thing. So it looks like this needs to be self contained.

Plugins are an absolute no.

(Other little edit: Whoops, I put INDEX/MATCH above when I meant FILTER/MATCH.)

EDIT: Hopefully this will help clarify?

Here's the structure of my main table where this formula resides:

My Formula takes the words from ColA and ColB and uses FILTER/MATCH to do a lookup that 1) returns multiple results, 2) the return results are entire rows, and 3) is an exact match (it returns nothing for words in ColA and ColB that don't exist in the lookup table).

This means that, as of the current state of the formula, I'm getting 2D arrays back for most of the rows in the main table. AFAICT, I'm not going to be able to stick these into a helper column somewhere as they'll overlap and just cause #SPILL which then won't evaluate in whatever formulas then reference them.

I need to turn these into 1D arrays that are 1 row x same-#-as-source columns where the result in each column is the product of only that column's contents. I'm then going to continue building off from this formula with more formula to achieve my final result that will fit nicely in the single cell. (I already have that piece solved. It's just going from the 2D -> 1D product array I need help with.)

CodePudding user response：

=LET(z,MyArray,a,INDEX(z,,1),b,INDEX(z,,2),c,INDEX(z,,3),d,INDEX(z,,4),CHOOSE({1,2,3,4},PRODUCT(a),PRODUCT(b),PRODUCT(c),PRODUCT(d)))

where MyArray is the construction which returns your 2D array, e.g.

{1,2,3,4;1,2,3,4;1,2,3,4}

The above assumes that MyArray returns an array comprising 4 columns. For each additional column beyond that number, an additional name and calculation parameter will be required within the LET function. What's more, the array passed to CHOOSE should be adjusted such that it comprises an array of integers from 1 up to the number of columns returned by MyArray.

For example, if MyArray returned instead:

{1,2,3,4,5;1,2,3,4,5;1,2,3,4,5}

the required construction would be:

=LET(z,MyArray,a,INDEX(z,,1),b,INDEX(z,,2),c,INDEX(z,,3),d,INDEX(z,,4),e,INDEX(z,,5),CHOOSE({1,2,3,4,5},PRODUCT(a),PRODUCT(b),PRODUCT(c),PRODUCT(d),PRODUCT(e)))

Edit: better is to replace the static array passed to CHOOSE with a SEQUENCE construction:

=LET(z,MyArray,a,INDEX(z,,1),b,INDEX(z,,2),c,INDEX(z,,3),d,INDEX(z,,4),e,INDEX(z,,5),CHOOSE(SEQUENCE(,COLUMNS(z)),PRODUCT(a),PRODUCT(b),PRODUCT(c),PRODUCT(d),PRODUCT(e)))

CodePudding user response：

So:

EDIT based on comment: If the sumproduct is in the wrong order, then the alternative is simple:

=SUMPRODUCT(D10:F10,D11:F11,D12:F12,D13:F13)


=sumproduct(A1:A4,B1:B4,C1:C4)

Then A1:A4 (and the other arrays) could be the index with match retrieving the data.

Or have the index and match in other cells and use those result in the sumproduct.

Had a play, making up some data as so:

In Cell D88:

=INDEX($B$2:$H$5,MATCH($C10,$A$2:$A$5,0),MATCH(D$9,$B$1:$H$1,0))

In cell D15:

=SUMPRODUCT(D10:D13,E10:E13,F10:F13)

In cells D9:F9 is data validation to choose the column heading A:G.