Sum with matrix functions?

[Aale de Winkel]

Using Excel in magic square research I created a lot of rather complicated
matrix statements doing simple things, summing a skew line I need to pick
each cell seperately. I thought using sum with a fairly simple matrix
function would save a lot of work attempts like
SUM(index(A1:F6;row()-row(a1)+(rowcel)+1;column()-column(a1)+(colcel)+1))
(discarding the function (rowcel) and (colcel) for this post which are
stements using the target row / column)
failed me, sum seems not to be matrix function capable, nor can I find a sum
version that would do the trick (is there any(?)) could matrix cabability be
added in future Excel.
Small matrices aren't a problem if course but for magic squares of higher
order using matrix functions within sum() would save a lot of work copying
and pasting.

 

[Luke M]

Rather than trying to use the SUM function, would either of these functions
work for what your trying to do (it was a little unclear to me, and it's been
awhile since I've used matrices regularly)

MINVERSE, MDETERM, MMULT

These were the first few hits I got when doing a XL help file search for
"matrix".

 

[Bernie Deitrick]

Aale,

You could use an array formula (entered using Ctrl-Shift-Enter) like this to sum the diagonal of a
10x10 matrix in D5:M14

=SUM((D5:M14*((ROW(D5:M14)-1)=(COLUMN(D5:M14)))))

Note that the ROW term uses -1 because the diagonal is offset from the sheet's diagonal by one row
(D5 instead of D4 as the top-left) you could also compensate by

=SUM((D5:M14*((ROW(D5:M14))=(COLUMN(D5:M14)+1))))

You can get other skew lines by using combinations of two formulas, like

=SUM((D5:M14*((ROW(D5:M14))=(COLUMN(D5:M14)))))+SUM((D5:M14*((ROW(D5:M14)-10)=(COLUMN(D5:M14)))))

also entered using Ctrl-Shift-Enter.


HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Another option is to use a same-sized matrix to assign cells a 'skew group'number, and use a formula
like

=SUMPRODUCT(Matrix*(SkewNumbers=1))

You could have the Skew Index increment when copied by using something like this

=SUMPRODUCT($B$2:$F$6*($B$8:$F$12=ROW(A1)))

and then copying down
(if your skew groups are, say, 1 to 5)


HTH,
Bernie
MS Excel MVP

 

[Aale de Winkel]

30 2-Pan 22 30 38 30
38 00 03 12 15 30
30 13 14 01 02 30
22 07 04 11 08 30
10 09 06 05 30
30 30 30 30

te above pasted as an order 4 example. 1st column sums diagonal directions,
1st row right of "2-Pan" the subdiagonal direction further sums in
horizontal and vertical. here sums indicate every other "broken-"
(sub-)diagonal magic (pe 30=3+13+8+6)

thanks Bernie, still experimenting.
=SUM((D5:M14*((ROW(D5:M14)-1)=COLUMN(D5:M14)+D2)))
varying d2 showed me that things aren't evaluated modular 10
=SUM((D5:M14*((ROW(D5:M14)-1)=COLUMN(D5:M14)+(COLUMN()-COLUMN(E4)))))
pasted in E4:N4 seems to horsejump D4-E7.....
never seen this form of expressions, looks promessing though. even if the
1st form above needs to be copied single cell. and needs some rethinking to
wrap around.
Other forms not tried yet, experimentation I'll continue this weekend.

Aale

 

[Aale de Winkel]

=SUM((D5:M14*((ROW(D5:M14)-1-(D4-10))=(COLUMN(D5:M14)))))+SUM((D5:M14*((ROW(D5:M14)-1-D4)=(COLUMN(D5:M14)))))

just wanted to add the above gave me the broken diagonal sum entered in cel
D4, changing D4 into the index of the target row / column doesn't seem to
work though.

 

[Bernie Deitrick]

Aale,

It works for me - with D4 equal to 3, I get the sum of the cells K5, L6, M7, D8, E9, F10, G11, H12,
I13, J14

Note that if you have a well behaved square of values, changing the index number will not
necessarily change the sum, so try entering slightly more randomized or staggered values.

HTH,
Bernie
MS Excel MVP

 

[Aale de Winkel]

The last statement works fine and trust me after decades of working with
magic squares (see: www.magichypercubes.com/Encyclopedia) I know your
repsonse here. what I looked for is replacening D4 with something like
"column()-column(D4)" in D4:M4 (or rather some column given the
'main-diagonal direction") this value thus serving as selector which broken
diagonal is summed over.
This doesn't seem to work, it looks like the statement is a single cel
target formula.
I therefore reckon copying the statement (along the target row/column) and
placing an index array somewhere is the way to go here, the statement is
already saving a lot of correcting during the copy paste sequence.
I currently haven't the time, but I'll experiment during the weekend with it.
(as said this kind of statement is new to me, I merely use Excell for idea
forming, but my friends use it more frequently)

Aale.

 

[Bernie Deitrick]

Aale,

....perhaps

=column()-column($D$4)

so that the cell reference doesn't increment when copied.

Or, you could use =Column(A1)-1

and have the cell reference increment.

HTH,
Bernie
MS Excel MVP