Discussion Re: Product of numbers in a row in a dynamic array in Excel
https://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2842737#M117832
<P><LI-USER uid="214174"></LI-USER> I can't quite match/figure out how to match your suggestion to my structure. (This is a lot of hoops to get through given that ByRow is coming! But I don't have access to it.)</P><P> </P><P>Here's what's really happening. I have a list of donors who will give to any of a list of nonprofits if certain criteria match between the donor's rules and criteria about the nonprofit. Each donor specifies criteria the she excludes or allows. For example, "allow North Texas, exclude Oklahoma, allow women's causes, exclude animal causes, allow this nonprofit, exclude that nonprofit."</P><P> </P><P>I can pick a particular nonprofit, look up all its criteria, match it to the allow/exclude rules for the donors, and a complicated formula creates an array that looks like this:</P><P> </P><P><SPAN> criteria1 criteria2 criteria3</SPAN></P><P><SPAN>donor1 exclude no rule allow</SPAN></P><P><SPAN>donor2 allow no rule no rule</SPAN></P><P><SPAN>donor3 no rule no rule no rule</SPAN></P><P> </P><P><SPAN>One or more excludes should exclude the donor, otherwise one or more allows should match the donor. No excludes and no allows also means the donor is not matched. Multiplication is a one-to-one map onto this logic if 0 is exclude, 1 is no rule, and >1 is allow.</SPAN></P><P> </P><P><SPAN> criteria1 criteria2 criteria3</SPAN></P><P><SPAN>donor1 0 1 2</SPAN></P><P><SPAN>donor2 2 1 1</SPAN></P><P><SPAN>donor3 1 1 1</SPAN></P><P> </P><P><SPAN>By multiplying the numbers together, the result is any number >=2 means the donor can support this nonprofit. Any single exclude will result in 0. Any non-rule leaves the result alone (1). Any allow (2) will get the result out of the 0/1 range.</SPAN></P><P> </P><P><SPAN>Since my result set is an array but not a table, I'm not sure how to use your filter suggestion. I mean "exclude any 0 in the row."</SPAN></P><P> </P><P>Eventually this will all end up in a database where it belongs, but boy Excel makes a proof of concept extremely fast and easy.</P><P> </P><P>By the way, your comment about these lambdas being unlike anything seen in spreadsheets before, I had a thought about that. The array functions make the spreadsheet data much more like a database. Maybe one day there will be an integration of SQL directly into the spreadsheet. But I think it's interesting that these two things intersect: (a) lambda - row by row calculation - is exactly what you attempt to avoid in a query, and conversely (b) the "instant total 2d reduction" that I'm trying to get around here is actually quite difficult to make happen in SQL. So this whole powerful array/lambda paradigm seems to be at the intersection of the gap between (older) spreadsheets and SQL. </P>Wed, 13 Oct 2021 16:40:08 GMTboukasa2021-10-13T16:40:08ZProduct of numbers in a row in a dynamic array
https://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2836366#M117621
<P>How does one do row-by-row evaluations with a 2d dynamic array?</P><P> </P><P>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 the numbers in all rows of the array, reduced to a single number.</P><P> </P><P>My spilled array:</P><P> </P><P>0 2 2</P><P>2 1 1</P><P>3 3 2</P><P> </P><P>Result sought:</P><P> </P><P>0</P><P>2</P><P>18</P><P> </P><P>from</P><P> </P><P>0*2*2</P><P>2*1*1</P><P>3*3*2</P><P> </P><P>I've struggled mightily and I'm about to resort to some very ugly text manipulation plus a name with evaluate based on the fact that I know my numbers will all be single digits <LI-EMOJI id="lia_disappointed-face" title=":disappointed_face:"></LI-EMOJI> I really do not want to do this!</P><P> </P><P>=LET(<BR /> rows, ROW(array)-ROW(INDEX(array,1,1)),<BR /> width, COLUMNS(array),<BR /> astext, TEXTJOIN("*",,array)&"*",<BR /> MID(astext,rows*width*2+1,width*2-1)</P><P>)</P><P> </P><P>It seems absurd that I can make four rows of text, but not fours rows of results.</P><P> </P><P>Thanks for any insights.</P><P> </P><P> </P><P> </P>Tue, 12 Oct 2021 03:40:07 GMThttps://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2836366#M117621boukasa2021-10-12T03:40:07ZRe: Product of numbers in a row in a dynamic array
https://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2837276#M117661
You probably need the new BYROW function: <A href="https://support.microsoft.com/en-us/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb" target="_blank">https://support.microsoft.com/en-us/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb</A>Tue, 12 Oct 2021 09:46:08 GMThttps://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2837276#M117661Jan Karel Pieterse2021-10-12T09:46:08ZRe: Product of numbers in a row in a dynamic array
https://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2837438#M117669
<P><LI-USER uid="1173763"></LI-USER> </P><P>This is something I have been complaining about for a couple of years or so now but, as <LI-USER uid="22322"></LI-USER> 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.</P><P> </P><P>As an interim workaround, the multiplication of positive numbers may be performed by using logarithms</P><LI-CODE lang="excel-formula">= EXP(
MMULT(LN(array#), {1;1;1})
)</LI-CODE><P> </P>Tue, 12 Oct 2021 10:42:54 GMThttps://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2837438#M117669Peter Bartholomew2021-10-12T10:42:54ZRe: Product of numbers in a row in a dynamic array
https://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2837740#M117683
<P><LI-USER uid="1173763"></LI-USER> A Secong solution could be to get each dynamic columns using Index, and multiply them:</P><P> </P><P>=INDEX(H4#;SEQUENCE(ROWS(H4#));1)*INDEX(H4#;SEQUENCE(ROWS(H4#));2)*INDEX(H4#;SEQUENCE(ROWS(H4#));3)</P><P> </P><P>/Geir</P>Tue, 12 Oct 2021 12:28:44 GMThttps://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2837740#M117683Geir Hogstad2021-10-12T12:28:44ZRe: Product of numbers in a row in a dynamic array
https://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2838178#M117702
<P>Thanks so much for this workaround! I don't have access to the BYROW function yet, but your solution definitely works for my use case for now. My column count is dynamic so I've made your formula:<BR /><BR />=EXP(MMULT(LN(array#),TRANSPOSE(SEQUENCE(1,COLUMNS(array#),1,0))))<BR /><BR />Much appreciated!</P>Tue, 12 Oct 2021 18:40:08 GMThttps://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2838178#M117702boukasa2021-10-12T18:40:08ZRe: Product of numbers in a row in a dynamic array
https://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2838202#M117703
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.Tue, 12 Oct 2021 14:47:33 GMThttps://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2838202#M117703boukasa2021-10-12T14:47:33ZRe: Product of numbers in a row in a dynamic array
https://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2839347#M117730
<P><LI-USER uid="1173763"></LI-USER> </P><P>I am not sure what you have in mind for this reply to <LI-USER uid="9195"></LI-USER>. INDEX will accept array and lift the appropriate scalar parameters, For example</P><P> </P><LI-CODE lang="excel-formula">= INDEX(array#, {2;5;3}, {4,2,5})</LI-CODE><P> </P><P>would assemble a 3x3 array from a larger array. It would still have the same problem as the original,</P><P><EM>i.e.</EM> functions like PRODUCT would still aggregate the entire array, giving a single value. For row products, you would still need </P><P> </P><LI-CODE lang="excel-formula">= BYROW(
INDEX(array#, {2;5;3}, {4,2,5}),
LAMBDA(row, PRODUCT(row))
)</LI-CODE><P> </P><P>I am just puzzled, not critical.</P>Tue, 12 Oct 2021 19:06:33 GMThttps://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2839347#M117730Peter Bartholomew2021-10-12T19:06:33ZRe: Product of numbers in a row in a dynamic array
https://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2839408#M117737
<P>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:<BR /><BR />In A1, put =SEQUENCE(5,5)<BR />In A7, put =ROW(A1#)-ROW(A1)+1<BR />In B7, put =INDEX(A1#,A7#,0)<BR />In H7, put =INDEX(A1#,A7,0)<BR /><BR />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.</P>Tue, 12 Oct 2021 19:29:42 GMThttps://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2839408#M117737boukasa2021-10-12T19:29:42ZRe: Product of numbers in a row in a dynamic array
https://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2839817#M117754
<P><LI-USER uid="1173763"></LI-USER> </P><P>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</P><P><STRONG>=INDEX(A1#,A7,0)</STRONG></P><P><SPAN>returns a range object; by that, <STRONG>COLUMN</STRONG> would return sheet column numbers and <STRONG>ISREF</STRONG> 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).</SPAN></P><P><SPAN>As for</SPAN></P><P><STRONG>=PRODUCT(INDEX(A1#,A7#,0))</STRONG></P><P><SPAN>that just suggests that Excel knew perfectly well what you wanted all along, but refused to give it to you!</SPAN></P><P> </P><P><SPAN>Even with the helper functions <STRONG>MAKEARRAY</STRONG> <EM>etc</EM>., it can be difficult to return arrays of arrays.</SPAN></P>Tue, 12 Oct 2021 21:22:10 GMThttps://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2839817#M117754Peter Bartholomew2021-10-12T21:22:10ZRe: Product of numbers in a row in a dynamic array
https://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2840278#M117766
Ha yes Excel reminds me of other relationships I have <LI-EMOJI id="lia_slightly-smiling-face" title=":slightly_smiling_face:"></LI-EMOJI><BR /><BR />I wanted to ask for your thoughts on one issue with the workaround formula you gave me. It doesn't work if there is a 0 in the array because ln(0) is an error. Is there a way to get that to work? I need the 0 result of the product as it has a semantic meaning.Wed, 13 Oct 2021 02:02:59 GMThttps://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2840278#M117766boukasa2021-10-13T02:02:59ZRe: Product of numbers in a row in a dynamic array
https://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2841370#M117792
<P><LI-USER uid="1173763"></LI-USER> </P><P>"... other relationships ..." big grin! </P><P> </P><P>The solution depends upon the semantic meaning. Taking it as zero, rather than omitted:</P><LI-CODE lang="excel-formula">= LET(
selectedValues, FILTER(values,Tabell1[Option]=selected),
ones, SEQUENCE(COLUMNS(selectedValues),,,0),
logs, LN(ABS(selectedValues)),
zeroTest, ISERROR(logs),
arr, IF(zeroTest, 0, logs),
result, MMULT(arr, ones),
isZero, MMULT(SIGN(zeroTest), ones),
IF(isZero, 0, EXP(result)))</LI-CODE><P>Once you have Lambda functions</P><LI-CODE lang="excel-formula">= LET(
PRODUCTλ, LAMBDA(row, PRODUCT(row)),
BYROW(array#, PRODUCTλ))</LI-CODE><P>would be preferable. An interesting features of these solutions is that neither looks remotely similar to a traditional spreadsheet formula.</P>Wed, 13 Oct 2021 10:38:54 GMThttps://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2841370#M117792Peter Bartholomew2021-10-13T10:38:54ZRe: Product of numbers in a row in a dynamic array
https://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2842737#M117832
<P><LI-USER uid="214174"></LI-USER> I can't quite match/figure out how to match your suggestion to my structure. (This is a lot of hoops to get through given that ByRow is coming! But I don't have access to it.)</P><P> </P><P>Here's what's really happening. I have a list of donors who will give to any of a list of nonprofits if certain criteria match between the donor's rules and criteria about the nonprofit. Each donor specifies criteria the she excludes or allows. For example, "allow North Texas, exclude Oklahoma, allow women's causes, exclude animal causes, allow this nonprofit, exclude that nonprofit."</P><P> </P><P>I can pick a particular nonprofit, look up all its criteria, match it to the allow/exclude rules for the donors, and a complicated formula creates an array that looks like this:</P><P> </P><P><SPAN> criteria1 criteria2 criteria3</SPAN></P><P><SPAN>donor1 exclude no rule allow</SPAN></P><P><SPAN>donor2 allow no rule no rule</SPAN></P><P><SPAN>donor3 no rule no rule no rule</SPAN></P><P> </P><P><SPAN>One or more excludes should exclude the donor, otherwise one or more allows should match the donor. No excludes and no allows also means the donor is not matched. Multiplication is a one-to-one map onto this logic if 0 is exclude, 1 is no rule, and >1 is allow.</SPAN></P><P> </P><P><SPAN> criteria1 criteria2 criteria3</SPAN></P><P><SPAN>donor1 0 1 2</SPAN></P><P><SPAN>donor2 2 1 1</SPAN></P><P><SPAN>donor3 1 1 1</SPAN></P><P> </P><P><SPAN>By multiplying the numbers together, the result is any number >=2 means the donor can support this nonprofit. Any single exclude will result in 0. Any non-rule leaves the result alone (1). Any allow (2) will get the result out of the 0/1 range.</SPAN></P><P> </P><P><SPAN>Since my result set is an array but not a table, I'm not sure how to use your filter suggestion. I mean "exclude any 0 in the row."</SPAN></P><P> </P><P>Eventually this will all end up in a database where it belongs, but boy Excel makes a proof of concept extremely fast and easy.</P><P> </P><P>By the way, your comment about these lambdas being unlike anything seen in spreadsheets before, I had a thought about that. The array functions make the spreadsheet data much more like a database. Maybe one day there will be an integration of SQL directly into the spreadsheet. But I think it's interesting that these two things intersect: (a) lambda - row by row calculation - is exactly what you attempt to avoid in a query, and conversely (b) the "instant total 2d reduction" that I'm trying to get around here is actually quite difficult to make happen in SQL. So this whole powerful array/lambda paradigm seems to be at the intersection of the gap between (older) spreadsheets and SQL. </P>Wed, 13 Oct 2021 16:40:08 GMThttps://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2842737#M117832boukasa2021-10-13T16:40:08ZRe: Product of numbers in a row in a dynamic array
https://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2843840#M117876
<P><LI-USER uid="1173763"></LI-USER> </P><P>That changes everything. You are not really interested in the product at all; it is merely an AND condition looking for an instance of "exclude". Negate the condition and it can be combined by addition, representing OR. </P><P>I have left the table because it resizes to accommodate fresh data, but no longer use the structured references.</P><LI-CODE lang="applescript">= LET(
selectedValues, FILTER(values,Tabell1[Option]=selected),
ones, SEQUENCE(COLUMNS(selectedValues),,,0),
inc, SIGN(selectedValues="allow"),
exc, SIGN(selectedValues="exclude"),
included, MMULT(inc, ones),
excluded, MMULT(exc, ones),
IF(NOT(excluded) * included, "Match", "--- ")
)</LI-CODE><P>or with Lambda functions</P><LI-CODE lang="excel-formula">= LET(
INCLUDEλ, LAMBDA(row, OR(row="allow")),
EXCLUDEλ, LAMBDA(row, OR(row="exclude")),
excluded, BYROW(array#, EXCLUDEλ),
included, BYROW(array#, INCLUDEλ),
IF(NOT(excluded) * included, "Match", "--- ")
)</LI-CODE><P> </P>Wed, 13 Oct 2021 21:48:24 GMThttps://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2843840#M117876Peter Bartholomew2021-10-13T21:48:24ZRe: Product of numbers in a row in a dynamic array
https://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2855156#M118306
Peter, yes this is the answer, and semantic. You have been incredibly generous with your time and knowledge. Thanks so much.Sun, 17 Oct 2021 23:34:56 GMThttps://techcommunity.microsoft.com/t5/excel/product-of-numbers-in-a-row-in-a-dynamic-array/m-p/2855156#M118306boukasa2021-10-17T23:34:56Z