CODEWITHSUNDEEP
excelexcel-formula
tobriand
tobriand

Reputation: 1167

Sum rows (or columns) of a 2d matrix into a 1d array in an Excel formula

Is there any way to tell specify to Excel that it should perform a per-row calculation within a 2d matrix? For instance, if I have an array defined

{1,0,0;0,0,1;1,0,1}

i.e.

1 0 0
0 0 1
1 0 1

Is there a formula that can allow me to operate on the array {1;1;2}?

The eventual goal in this would most likely to be to further transform that array using an expression such as {1;1;2}>=2 or MATCH(2,{1;1;2},0), but the important thing isn't so much what the outer expression is as that the transformation takes place at all.

Of course, much of this can in many cases be outsourced to operations on a helper column, but there are numerous times when solving a problem with space feels like an inelegant approach.

Upvotes: 2

Views: 3475

Answers (1)

Axel Richter
Axel Richter

Reputation: 61985

MMULT would do what you wants.

{=MMULT({1,0,0;0,0,1;1,0,1},{1;1;1})}

used as array formula (CSE) results in {1;1;2}.

1,0,0       1       1*1+0*1+0*1  =  1
0,0,1   x   1   =   0*1+0*1+1*1  =  1
1,0,1       1       1*1+0*1+1*1  =  2

When MMULT is wrapped in other functions which take their parameters as arrays, there may not be the need for pressing Ctrl+Shift+Enter. For example

=MATCH(2,MMULT({1,0,0;0,0,1;1,0,1},{1;1;1}),0)

results in 3 without entering the formula using Ctrl+Shift+Enter.

Upvotes: 5

Related Questions