Matrix Reverse with row identification to reverse bas

[PJ Karaffa]

This question is based off of a post that I read a while ago that
Harlan Grove answered. It was based off of a Matrix that was reversedd
an then summed.

My question is I have a matrix in A14 and I am looking to get a sum
from it but what I would like to do is have 2 of the rows(reverse) and
then get the sum from that. Here is the matrix that sums to 605.
1 5 4 6
2 3 3 3
4 5 2 7
1 3 5 2

column A (1*2*4*1) column B (5*3*5*3) etc.

If I used lets say cell f1 an g1 and placed 1 & 3 (for rows 1 an 3) to
reverse, I would get 555

6 4 5 1
2 3 3 3
7 2 5 4
1 3 5 2


Can someone help me. I would like to be able to sum the first example
and get 555.

 

[Harlan Grove]

My question is I have a matrix in A14 and I am looking to get a sum
from it but what I would like to do is have 2 of the rows(reverse) and
then get the sum from that. Here is the matrix that sums to 605.
1 5 4 6
2 3 3 3
4 5 2 7
1 3 5 2

column A (1*2*4*1) column B (5*3*5*3) etc.

If I used lets say cell f1 an g1 and placed 1 & 3 (for rows 1 an 3) to
reverse, I would get 555

6 4 5 1
2 3 3 3
7 2 5 4
1 3 5 2

....

If the original matrix were named MAT, try the array formula

=SUM(EXP(MMULT(TRANSPOSE(ROW(MAT))^0,LN(MMULT(MAT,--(ROW(MAT)
+TRANSPOSE(ROW(MAT))=CELL("Row",MAT)+MAX(ROW(MAT))))*{1;0;1;0}
+MAT*(1-{1;0;1;0})))))

Note that I'm using a hardcoded array, {1;0;1;0}, to select which rows to
reverse. This is most flexible, but it may be safer to replace both
instances of this array with a defined name.

 

[PJ Karaffa]

...

If the original matrix were named MAT, try the array formula

=SUM(EXP(MMULT(TRANSPOSE(ROW(MAT))^0,LN(MMULT(MAT,--(ROW(MAT)
+TRANSPOSE(ROW(MAT))=CELL("Row",MAT)+MAX(ROW(MAT))))*{1;0;1;0}
+MAT*(1-{1;0;1;0})))))

Note that I'm using a hardcoded array, {1;0;1;0}, to select which rows to
reverse. This is most flexible, but it may be safer to replace both
instances of this array with a defined name.

Harlan,

Thank you very much it works like a charm. I have never used the LN
Function. Very Nice! Thank you again for your guidance and assistance

 

[Peo Sjoblom]

...

If the original matrix were named MAT, try the array formula

=SUM(EXP(MMULT(TRANSPOSE(ROW(MAT))^0,LN(MMULT(MAT,--(ROW(MAT)
+TRANSPOSE(ROW(MAT))=CELL("Row",MAT)+MAX(ROW(MAT))))*{1;0;1;0}
+MAT*(1-{1;0;1;0})))))

Note that I'm using a hardcoded array, {1;0;1;0}, to select which rows to
reverse. This is most flexible, but it may be safer to replace both
instances of this array with a defined name.

Just for fun I wanted to know if it was possible using other functions

=SUMPRODUCT(N(OFFSET(INDIRECT(CELL("address",INDEX(Mat,,COLUMNS(Mat)))),0,-1
*COLUMN(Mat)+1)),INDEX(Mat,2,),N(OFFSET(INDIRECT(CELL("address",INDEX(Mat,3,
COLUMNS(Mat)))),0,-1*COLUMN(Mat)+1)),INDEX(Mat,4,))


Regards,

Peo Sjoblom

 

[Harlan Grove]

Just for fun I wanted to know if it was possible using other functions

=SUMPRODUCT(N(OFFSET(INDIRECT(CELL("address",INDEX(Mat,,COLUMNS(Mat)))),0,
-1*COLUMN(Mat)+1)),INDEX(Mat,2,),N(OFFSET(INDIRECT(CELL("address",
INDEX(Mat,3,COLUMNS(Mat)))),0,-1*COLUMN(Mat)+1)),INDEX(Mat,4,))

If you're essentially going to hardcode each row into SUMPRODUCT, you could
shorten this to

=SUMPRODUCT(
N(OFFSET(MAT,0,COLUMNS(MAT)-COLUMN(MAT),1,1)),
INDEX(MAT,2,0),
N(OFFSET(MAT,2,COLUMNS(MAT)-COLUMN(MAT),1,1)),
INDEX(MAT,4,0))