Matrix operations

[sensorflo]

Hello

I would like to apply a formula operating on matrices/vectors to a
number of rows.

Formula:
T1*(v1+T2*v2)

Input:
T1 is a 2x2 Matrix, v1 a 2x1 vector, both are defined through cells
which are always at the same location within the Worksheet. For example
T1 in A1:B2, v1 in A3:B3.
T2 is a 2x2 Matrix defined by { cos(x),sin(x); -sin(x),cos(x)}.
x is a value, v2 a 2x1 vector. See below how they are defined

Output:
A 2x1 vector

Task:
I now want to apply the formula to a number of rows. In each row the
column 1 to 2 define v2, column 3 defines x. The result of the formula
should be in column 4 and 5.

How would you do that? Thank you for your help

Florian Kaufmann


Its not that I want you to do my work without trying myself first.
That's what I tried so far

My first try:
---------------
I gave the name T1 to the range A1:B2, v1 to A3:B3.
In A4:B4 is v2, in C4 is x.

In C5 I put
=INDEX( MMULT( T1 ; v1 + MMULT( {cos(C4),sin(C4); -sin(C4),cos(C4)};
A4:B4 ) ) ; 1 )

In C6 I put
=INDEX( MMULT( T1 ; v1 + MMULT( {cos(C4),sin(C4); -sin(C4),cos(C4)};
A4:B4 ) ) ; 2 )

However, it turns out that a construct like {cos(C4),sin(C4);
-sin(C4),cos(C4)} is not possible, since arrays of type {...} must be
constant. They can not contain references to cells as I did with C4.

Second try:
------------
I tried to write a VB Function which does the job. But since I am an
absolute beginner in VB, I have various troubles.
- How to call my function from a cell. I always get "#Name?"
- How to define T2 within the function
- There seems to be a different between Ranges and Arrays, and I don't
know where it is. I get errors passing ranges to mmult.

 

[sensorflo]

I have to add that in fact the formula is more complex and the
vectors/matrices have higher dimensions. That's why I want to do it
with matrix/vector operations. That is instead of
INDEX(MMULT(A1:B2;A3:A4);1) I don't want to write A1*A3+B1*A4.

 

[delfiled]

Array T2 must exist as a range of cells.
Below are two approaches.

Using Cell Addressing
=====================

T1 is B1:C2
v1 is B4:B5
v2 is B7:B8
T2 is B12:C13
y is B15:B16

insert your numerical values into T1,V1,V2,T2

into cell B15 insert =MMULT(B1:C2,B4:B5+MMULT(B12:C13,B7:B8))
drag/copy B15 into B16
select B15:B16
type cntrl-shift-enter


Using Named Ranges
==================

name B1:C2 as matT1
name B4:B5 as matV1
name B7:B8 as matV2
name B12:C13 as matT2

into cell B15 insert =MMULT(matT1,matV1+MMULT(matT2,matV2))
drag/copy B15 into B16
select B15:B16
type cntrl-shift-enter

dave y.

 

[sensorflo]

The problem with this approach however is that I don't have the place
for the extra cells that T2 needs. I very much liked to calculate T2
within the formula.

Maybe I have to say what this is all about. The sheet converts
coordinates between different coordinate systems. Look at the 'excel
sheet' below

A B C D E F G
1 T1: 1 0
2 0 1
3 V1: 1
4 1
5
8 |-sys1 -----------------|-sys2------------------
9 x y t x y t
10 |-----------------------|-----------------------
11 0 0 0 =f12x() =f12y() =f12t()
12 0 1 1 =f12x() =f12y() =f12t()
.........
20 =f21x() =f21y() =f21t() 0 0 0
21 =f21x() =f21y() =f21t() 0 1 1


The first part contains the parameters of the Transformation. That
would be T1 = B1:C2, V1 = B3:B4. The rest is a list of coordinates. In
each row coordinates are given in one system and calculated in the
other one. v1 would now be the 2 cells in the x y column, x (as in my
first posting) would be the t column. Note that the x in this posting
is not the x in the first posting - sorry for the confusion.

The formulas f12x() etc only symbolise the actual formulas and shows
which cells contain the same formulas. An actual example would be (for
D11):
=INDEX( MMULT( $B$1:$C$2 ; $B$3:$B$4 + MMULT( {cos(C11),sin(C11);
-sin(C11),cos(C11)}; A11:B11 ) ) ; 1 )

However, as said in my first posting, this is not possible.

As said the purpose is to transform any coordinates between the
systems. That is it is not known in advance which row converts from
which system into which. The user fills in values for the coordinates
of one system and then copies the required formulas into the other
columns of the same row. So he gets his custom list of coordinates.

Now one can see that I don't have space to put T2 into any cells. Each
Row would need a 2*2 cell block as temporary storage for T2.

Again, this is simplified. Actually I have 3 systems, each has 4
dimensions, and there are about 4 Matrices and 4 Vectors being the
parameters of the transformation.

Greetings

Florian Kaufmann