PDA

View Full Version : Need help on Matrix multiplication



arrun
12-20-2007, 12:17 AM
I have an array of numbers in cells : A1, B1, and C1. I also have another matrix of 2 rows and 2 columns in A4:B5. Without altering anything, I want to perform a matrix multiplication without taking consideration B1. Can anyone tell me how to do that? : pray2:

Regards,

agarwaldvk
12-20-2007, 01:55 AM
Dear arrun

Given your problem, I don't think you can carry out matrix multiplication.

For you to be able to do matrix multiplication, from what I know, the number of columns of the first matrix needs to be equal to the number of rows of the second.

If you were to reconfigure your 2 matrices such that the above condition is met for example, if the first matrix is a A(m by n) (say 2 by 3) matrix and second is B(n by p) ( 3 by 2) matrix then the resulting matrix will be a m by p matrix (2 by 2).

This is how :-

The first element (1,1) is computed by taking the sum of the product of each element of the first row in the first matrix by each element of the first column in the second matrix.


Similarly, the second element (1,2) is computed by taking the sum of the product of each element of the first row in the first matrix by each element of the second column in the second matrix.

And so on!


Hope this helps!


Best regards


Deepak

arrun
12-20-2007, 02:06 AM
Noh Deepak, this thing I know. But I am looking the solution for this practical problem. Any other suggestion will be very helpful to me.

Regards,

unmarkedhelicopter
12-20-2007, 02:14 AM
Deepak has covered the basics for matrix multiplication, you can NOT multiply a (1,2) by a (2,2) by normal means.
This means what you want done is not normal ...
if they we letters i.e. 1st (1,2) = (a,b) and 2nd (2,2) = (c,d _ e,f)
what would be the sequence of multiplications and additions that would result in (whatever ?) sized matrix you require ? or just a single figure ?

arrun
12-20-2007, 02:25 AM
I know I cannot do any matrix calculation using un-appropriate order. That is why I want to skip B1. I am looking some excel function which will skip some elements of a array, to reduce it's order. For example here the order is 3; A1, B1, C1. Now if I could delete B1, then I have A1 C1. Then I can do matrix multiplication.

However I do not want to delete physically because, A1, B1, C1 are being used in some other calculation.

If you need further clarification please let me know.

Regards,

unmarkedhelicopter
12-20-2007, 03:47 AM
If you need further clarification please let me know.I did require further clarification, that is why I asked you the question I did. Do you want me to repeat it, rephrase it or could you just go back and read it ?

Bob Phillips
12-20-2007, 04:59 AM
Perhaps this UDF will help you.

The syntax is

=MMULT_ext(array1, array2 [,ignore_cell[,ignore_cell[...]]]

so you would use

=MMULT_ext(A1:C1,A2:B5,B1)



Function MMULT_ext(rng1 As Range, rng2 As Range, ParamArray ignore())
Dim ary
Dim aryRow, aryCol
Dim fIgnore As Boolean
Dim mpRow As Range, mpCol As Range
Dim mpRowIdx As Long, mpColIdx As Long
Dim tmp
Dim rng As Range
Dim i As Long, j As Long, k As Long, l As Long, m As Long
Dim cRows As Long, cCols As Long

ReDim ary(1 To rng1.Rows.Count, 1 To rng2.Columns.Count)

For j = LBound(ary, 2) To UBound(ary, 2)

For i = LBound(ary, 1) To UBound(ary, 1)

ReDim aryRow(1 To rng1.Rows(i).Cells.Count)
m = 0
For k = 1 To rng1.Rows(i).Cells.Count

fIgnore = False
For l = LBound(ignore) To UBound(ignore)

If Not Intersect(rng1.Cells(i, k), ignore(l)) Is Nothing Then
fIgnore = True
Exit For
End If
Next l
If Not fIgnore Then

m = m + 1
aryRow(m) = rng1.Cells(i, k)
End If
Next k
ReDim Preserve aryRow(1 To m)

ReDim aryCol(1 To rng1.Rows(i).Cells.Count)
m = 0
For k = 1 To rng2.Columns(i).Cells.Count

fIgnore = False
For l = LBound(ignore) To UBound(ignore)

If Not Intersect(rng2.Cells(i, k), ignore(l)) Is Nothing Then
fIgnore = True
Exit For
End If
Next l
If Not fIgnore Then

m = m + 1
aryCol(m) = rng2.Cells(k, j)
End If
Next k

ReDim Preserve aryCol(1 To m)
For k = 1 To UBound(aryRow, 1)

ary(i, j) = ary(i, j) + aryRow(k) * aryCol(k)
Next k
Next i
Next j

Set rng = Application.Caller
ReDim tmp(1 To rng1.Rows.Count, 1 To rng2.Columns.Count)

cRows = IIf(UBound(tmp, 1) > UBound(ary, 1), UBound(ary, 1), UBound(tmp, 1))
cCols = IIf(UBound(tmp, 2) > UBound(ary, 2), UBound(ary, 2), UBound(tmp, 2))

For i = LBound(ary, 1) To cRows
For j = LBound(ary, 2) To cCols
tmp(i, j) = ary(i, j)
Next j
Next i

For i = LBound(tmp, 1) To UBound(tmp, 1)
For j = UBound(ary, 2) + 1 To UBound(tmp, 2)
tmp(i, j) = ""
Next j
Next i

For j = LBound(tmp, 2) To UBound(tmp, 2)
For i = UBound(ary, 1) + 1 To UBound(tmp, 1)
tmp(i, j) = ""
Next i
Next j

MMULT_ext = tmp

End Function

arrun
12-20-2007, 05:28 AM
Thanx Xld. I exactly looking for this type of help. However I am struggling to figure out all those steps. If I get any difficulties, I will get back to you.

Regards,

Bob Phillips
12-20-2007, 09:54 AM
My solution was a bit generic, so whilst waiting for a phone call I had some spare time, and came up with



Function MMULT_ext(rng1 As Range, rng2 As Range, ParamArray ignore())
Dim ary
Dim aryRow, aryCol
Dim mpRow As Range, mpCol As Range
Dim mpRowIdx As Long, mpColIdx As Long
Dim tmp
Dim rng As Range
Dim i As Long, j As Long, k As Long, l As Long, m As Long
Dim cRows As Long, cCols As Long

ReDim ary(1 To rng1.Rows.Count, 1 To rng2.Columns.Count)

For j = LBound(ary, 2) To UBound(ary, 2)

For i = LBound(ary, 1) To UBound(ary, 1)

aryRow = ExcludeCells(rng1, False, i, ignore())

aryCol = ExcludeCells(rng2, True, j, ignore())

For k = 1 To UBound(aryRow, 1)

ary(i, j) = ary(i, j) + aryRow(k) * aryCol(k)
Next k
Next i
Next j

Set rng = Application.Caller
ReDim tmp(1 To rng1.Rows.Count, 1 To rng2.Columns.Count)

cRows = IIf(UBound(tmp, 1) > UBound(ary, 1), UBound(ary, 1), UBound(tmp, 1))
cCols = IIf(UBound(tmp, 2) > UBound(ary, 2), UBound(ary, 2), UBound(tmp, 2))

For i = LBound(ary, 1) To cRows
For j = LBound(ary, 2) To cCols
tmp(i, j) = ary(i, j)
Next j
Next i

For i = LBound(tmp, 1) To UBound(tmp, 1)
For j = UBound(ary, 2) + 1 To UBound(tmp, 2)
tmp(i, j) = ""
Next j
Next i

For j = LBound(tmp, 2) To UBound(tmp, 2)
For i = UBound(ary, 1) + 1 To UBound(tmp, 1)
tmp(i, j) = ""
Next i
Next j

MMULT_ext = tmp

End Function

Private Function ExcludeCells(ByRef rng, ByVal Invert As Boolean, _
ByVal CellIndex As Long, ParamArray ignore()) As Variant
Dim tmp As Variant
Dim mpIndex As Long
Dim mpInclude As Long
Dim mpIgnore As Long
Dim fIgnore As Boolean

ReDim tmp(1 To rng.Rows(CellIndex).Cells.Count)
mpInclude = 0

If Invert Then

For mpIndex = 1 To rng.Columns(CellIndex).Cells.Count

fIgnore = False
For mpIgnore = LBound(ignore(0)) To UBound(ignore(0))

If Not Intersect(rng.Cells(mpIndex, CellIndex), ignore(0)(mpIgnore)) Is Nothing Then

fIgnore = True
Exit For
End If
Next mpIgnore

If Not fIgnore Then

mpInclude = mpInclude + 1
tmp(mpInclude) = rng.Cells(mpIndex, CellIndex)
End If
Next mpIndex
Else

For mpIndex = 1 To rng.Rows(CellIndex).Cells.Count

fIgnore = False
For mpIgnore = LBound(ignore(0)) To UBound(ignore(0))


If Not Intersect(rng.Cells(CellIndex, mpIndex), ignore(0)(mpIgnore)) Is Nothing Then

fIgnore = True
Exit For
End If
Next mpIgnore

If Not fIgnore Then

mpInclude = mpInclude + 1
tmp(mpInclude) = rng.Cells(CellIndex, mpIndex)
End If
Next mpIndex
End If

ReDim Preserve tmp(1 To mpInclude)
ExcludeCells = tmp
End Function