Excel Array Formula Series 12: MMULT function Matrix Algebra

Hi there, I am just wondering if excel can do matrix symbolic algebra ? and how ?

I have many matrices i need to multiply to each other and they get really2 messy when i did it manually

thank you

I am not getting the expect result when I multiply two arrays

Array1
1 5 2 4
1/5 1 1/2 1/2
1/2 2 1 2
1/4 2 1/2 1


Array2
0.5115
0.0986
0.2433
0.1466


Expected Result
2.0774
0.3958
0.9894
0.5933

My result
2.0774
2.0774
2.0774
2.0774


I am sure I am entering them as array formulas but I am not getting the right answer. I am sure this is something simple. Thanks for your help.

Hi,

In the part of my VBA program, I need a matrix multiplication for the selected range of the matrix in a worksheet.

I tried the following which doesn't work at all:

Dim M1Range As Double
Dim M2(7,7) As Double
Worksheets("sheet1").Activate
M1Range=Range("B5:I12")
M2 = WorksheetFunction.MMult(M1Range, M1Range)

Any help to figure out this problem would very much appreciated.

Hi All,

For filtering out unique values from a column and listing them all together in an adjacent column, this formula I found on another forum: {=OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$15)=B$1:B1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)}

works just great, yet I am trying to understand exactly how its middle part works, but haven't been able to so far!
Maybe I am a bit behind on the math involved here, but could somebody please explain how the matrix transposition and multiplication works in this case, in particular - how can he compare that transposed original list is equal to part of new filtered list? and what is the second array in the MMULT(array1,array2) in this case, it seems it would be all 1/1's, or is it getting into matrix division here somehow?

I tried taking the formula apart into pieces, but after several hours still am stuck with the middle part...

I take the liberty to quote part of this 2005 post http://www.excelforum.com/tips-and-t...ue-values.html by Alan, describing the formula's use:
"... If you have a list in A1:A13 as
follows:

Alan
Bob
Charlie
Alan
Alan
Alan
Charlie
Dave
Charlie
Bob
Edgar
Bob
Bob

In
B1 enter either "Alan" or link to A1

In B2 enter this as an array
formula:

{=OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$13)=B$1:B1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)}

To
enter the array formula, type it in without the curly brackets, and then enter
using Shift-Ctrl-Enter and excel will put the braces on the formula.

Copy
that formula down to B2:B13.

It returns 'Not Applicable' in B6:B13 since there are no more unique entries. ..."



It seems that Alan has not posted there for a long time, so I am asking in this forum, which appears quite active and hoping with gratitude to be enlightened.

Roman

Hi everyone,
I am trying to perform a principal components analysis on 1116 records with 10 variables. I am doing this by computing Eigen Values and multiplying them (MMULT) by my 10 standardized variables. Apparently in Excel 2003 (which is what I use) the array for an MMULT function cannot exceed 5000-something cells.
Do you know of a way to perform a PCA on a large array, or to use the MMULT function on a large array?
Would updating my version of Excel work?
Thanks.

Hi All

I am trying to do a matrix multiplication of two named ranges, one called "InputMatrix", the other called "Weights", into a user defined function called Optimise. The "InputMatrix" is N x M and the "Weights" is N x 1 in size. The output is therefore a matrix of M x 1 in size and requires that the Weights range be transposed.

The function calls but the output is not working. The output I am getting is #VALUE!.

The UDF I am using is as follows:

Function Optimise(InputMatrix As String, Weights As String) As Variant

Dim n As Integer
n = Cells(14, 20).Value
'Defines 'n' as dimension n of matrix of size n x m

Dim m As Integer
m = Cells(14, 21).Value
'Defines 'm' as dimension m of matrix of size n x m

Dim rangestring1, rangestring2 As String
'rangestring1 = InputMatrix
'rangestring2 = weights

rangestring1 = "=Sheet1!R8C3:R" & 7 + n & "C" & 2 + m
ThisWorkbook.Names.Add Name:="InputMatrix", RefersTo:=rangestring1
'Defines named range for InputMatrix

rangestring2 = "=Sheet1!R4C3:R" & 4 & "C" & 2 + m
ThisWorkbook.Names.Add Name:="Weights", RefersTo:=rangestring2
'Defines named range for Weights

Optimise = Application.WorksheetFunction.MMult(InputMatrix, Application.WorksheetFunction.MInverse(Weights))

End Function


Thank you for your help

G

Hi all,

Please can you help me with this problem I'm facing. I am creating a risk register and to assess a risk I am using a matrix (see file attached). I am not skilled enough with excel and arrays or whatever I need to do this so i am asking here. I basically need the value in the 'assessment' column to be populated in my register depedning on which values are entered in the 'probability' 'cost impact' and 'time impact' columns on the register. e.g. if the prob is 95, cost is 900,000 and time impact is 50 - VHI will be entered in the the assessment column.

Please shout if this doesnt make sense. i really hope you can help me with this.

many thanks.

Anthony.

I have got this code
Code:

Sub buscar()
Dim base As String
Dim strFact As String, strUnos As String, strPreZeros As String, strPostZeros As String
Dim iCardBase As Integer, idesplaz As Integer

base = Range("a1:a22").Address

For iCardBase = 1 To 22
strUnos = Application.Rept("1;", iCardBase)
For idesplaz = 1 To (22 - iCardBase + 1)
strPreZeros = Application.Rept("0;", idesplaz - 1)
strPostZeros = Application.Rept(";0", 22 - iCardBase - idesplaz + 1)
If Len(strPostZeros) = 0 Then
strUnos = Left(strUnos, Len(strUnos) - 1)
Else
strPostZeros = Right(strPostZeros, Len(strPostZeros) - 1)
End If
strFact = "{" & strPreZeros & strUnos & strPostZeros & "}"
sngValSuma = Application.Evaluate("=MMult(" & strFact & "," & base & ")")

Next idesplaz


Next iCardBase

End Sub

strFact is a array constant as string, with 22 elements

Running this statement
sngValSuma = Application.Evaluate("=MMult(" & strFact & "," & base & ")")
Obtain "Error 2015".
Thanks in advance

Hi,

I have a list of names in column B, separated by categories. Each category name is followed by list of names. Each category has different # of names under it.

For eg. a category name in cell B4 is followed by 6 names (B5:B10); then follows a blank cell (B11); then 2nd category name in a cell (B12) followed by 4 names (B13:B16); and so on and so forth.

I want to fill the column A with numbers against the names (in column B) only and not against the blank cells and category names.

I can fill the series using + sign or "=A5+1" which is not what I am looking for. When I add a name in any of the categories or delete a name from a category, the fill series has to be updated completly, from below the changed row, manually.

I tried the following array formula -

{=IF(AND(COUNTIF(B6,"*")>0,COUNTIF(B5,"*")>0),(IF(COUNTIF(B4,"*")>0,B5+1,B3+1)),"")}

This formula returns 1 in A5 but each subsequent cell in column A is giving #VALUE! except the cells in column A that have category name or blank cell in column B.

So it is doing one part i.e. not numbering cells against category names and blank cells but it is not adding the way I want them to.

Would greatly appreciate if someone can help me with this.

Thanks.

Hi guys,

I'm a total newbie and am trying to use mmult in VBA..
All i want to do is multiply 2 matrices (3x3 and 3x1), so that I can use that matrix for more complicated calculations.

Need help on a UDF that returns the final result as a matrix (i.e. 3x1 matrix)
Below is my code but its returning error (#VALUE!)
VB:

 
Function matt() 
    Dim P(1 To 3, 3) 
    P(1, 1) = 1 
    P(1, 2) = 2 
    P(1, 3) = 3 
    P(2, 1) = 4 
    P(2, 2) = 5 
    P(2, 3) = 6 
    P(3, 1) = 7 
    P(3, 2) = 8 
    P(3, 3) = 9 
     
     
    Dim Q(1 To 3, 1) 
    Q(1, 1) = -1 
    Q(2, 1) = -2 
    Q(3, 1) = -3 
     
    Dim result(1 To 3, 1) 
    result = Application.WorksheetFunction.MMult(P, Q) 
    matt = result 
End Function 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Thanks in advance!!!

Matt