To carry out matrix multiplication in Excel VBA, you need to use a nested loop construction to iterate over the weather of the matrix and carry out the required calculations. Right here is an instance of how you are able to do this:
submatrix multiply()
Declare variables for matrices.
dim A(1 to three, 1 to three) as double
Dim B (1 to three, 1 to three) as doublet
Dim C (1 to three, 1 to three) as double
Set values for the matrix.
A(1, 1) = 1: A(1, 2) = 2: A(1, 3) = 3
A(2, 1) = 4: A(2, 2) = 5: A(2, 3) = 6
A(3, 1) = 7: A(3, 2) = 8: A(3, 3) = 9
B(1, 1) = 9: B(1, 2) = 8: B(1, 3) = 7
B(2, 1) = 6: B(2, 2) = 5: B(2, 3) = 4
B(3, 1) = 3: B(3, 2) = 2: B(3, 3) = 1
Carry out matrix multiplication.
For i = 1 to three
For j = 1 to three
C(i, j) = 0
For ok = 1 to three
C(i, j) = C(i, j) + A(i, ok) * B(ok, j)
Subsequent ok
Subsequent J
Subsequent i
' Output the consequence to the sheet.
For i = 1 to three
For j = 1 to three
cells(i, j). worth = C(i, j);
Subsequent J
Subsequent i
Concluding Sub
This code multiplies the matrix AAnd BRetailer the consequence within the matrix C. The result’s then printed on the sheet.
Observe that this code assumes the matrices are 3×3, however you’ll be able to simply modify it to deal with different sizes by altering the scale of the arrays and the loop bounds.
What’s matrix multiplication in Excel?
In Excel, matrix multiplication is a mathematical operation that means that you can multiply two matrices (fields of numbers) and create a 3rd matrix in consequence. Matrix multiplication differs from common multiplication in that matrix components are multiplied primarily based on their place within the matrix and never on their values.
For instance, contemplate the next two metrics:
A = [ 1 2 3 4 5 6 ] B = [ 7 8 9 10 11 12 ]
To carry out matrix multiplication, we multiply the weather of the primary row of matrix A by the weather of the primary column of matrix B and sum the merchandise. This provides us the primary component of the ensuing matrix:
(1 * 7) + (2 * 9) + (3 * 11) = 58
We then repeat this course of for every component of the ensuing matrix, utilizing the suitable components from the rows and columns of the enter matrix. On this case the consequence matrix is:
C = [ 58 64 139 154 ]
In Excel, you’ll be able to carry out matrix multiplication utilizing formulation or writing customized features utilizing VBA (Visible Fundamental for Purposes). For instance, the method for matrix multiplication in Excel is , =MMULT(A1:C2,D1:E3)The place A1:C2and are the ranges of cells that include the enter matrix.D1:E3
Decide the matrix product AB
Discover the product of two matrices AAnd B, it is advisable to do matrix multiplication. Matrix multiplication multiplies matrix components primarily based on their positions within the matrix and never on their values.
Here is an instance of the best way to discover the product of two matrices AAnd B:
A = [ a11 a12 a13 a21 a22 a23 a31 a32 a33 ] B = [ b11 b12 b13 b21 b22 b23 b31 b32 b33 ] C = [ c11 c12 c13 c21 c22 c23 c31 c32 c33 ] C(1,1) = A(1,1) * B(1,1) + A(1,2) * B(2,1) + A(1,3) * B(3,1) C(1,2) = A(1,1) * B(1,2) + A(1,2) * B(2,2) + A(1,3) * B(3,2) C(1,3) = A(1,1) * B(1,3) + A(1,2) * B(2,3) + A(1,3) * B(3,3) C(2,1) = A(2,1) * B(1,1) + A(2,2) * B(2,1) + A(2,3) * B(3,1) C(2,2) = A(2,1) * B(1,2) + A(2,2) * B(2,2) + A(2,3) * B(3,2) C(2,3) = A(2,1) * B(1,3) + A(2,2) * B(2,3) + A(2,3) * B(3,3) C(3,1) = A(3,1) * B(1,1) + A(3,2) * B(2,1) + A(3,3) * B(3,1) C(3,2) = A(3,1) * B(1,2) + A(3,2) * B(2,2) + A(3,3) * B(3,2) C(3,3) = A(3,1) * B(1,3) + A(3,2) * B(2,3) + A(3,3) * B(3,3)
On this instance AAnd Bare each 3×3 matrices and the CThe result’s a 3×3 matrix. To seek out the product of those matrices, we use AMultiply the weather of every row by the corresponding components of every column and sum them BMerchandise.
Observe that to carry out matrix multiplication, the variety of columns within the Amatrix should equal the variety of rows within the matrix B. If not, the metric can’t be multiplied.
How do you subtract a matrix?
To subtract two matrices, you could subtract the corresponding components of every matrix. For instance, contemplate the next two metrics:
A = [ a11 a12 a13 a21 a22 a23 a31 a32 a33 ] B = [ b11 b12 b13 b21 b22 b23 b31 b32 b33 ]
To subtract these matrices, you’ll be able to merely subtract the corresponding components of every matrix:
C = [ a11-b11 a12-b12 a13-b13 a21-b21 a22-b22 a23-b23 a31-b31 a32-b32 a33-b33 ]
This creates a brand new matrix that’s the results of matrix CSubtraction.BA
Observe that to subtract two matrices, the matrices should be the identical dimension. If the matrices will not be the identical dimension, you can’t subtract.
In Excel, you’ll be able to subtract matrices through the use of formulation or writing customized features utilizing VBA (Visible Fundamental for Purposes). =MINUS(A1:C3,D1:F3)For instance, the matrix subtraction method in Excel is the place A1:C3and are the ranges of cells that include the enter matrix.D1:F3
Easy methods to discover the transpose of a matrix
The transpose of a matrix is a brand new matrix obtained by rotating the matrix round its diagonal. The ensuing matrix has the identical variety of rows as the unique matrix had columns and the identical variety of columns as the unique matrix had rows.
For instance, contemplate the next matrix:
Copy Code A = [ a11 a12 a13 a21 a22 a23 a31 a32 a33 ]
To seek out the transpose of this matrix, we are able to flip it diagonally to get the next matrix.
copy codeA' = [ a11 a21 a31 a12 a22 a32 a13 a23 a33 ]
On this instance, the A'Matrix is transposed A.
In Excel, you will discover the transpose of a matrix utilizing the perform TRANSPOSE. For instance to seek out the transpose of a matrix in vary A1:C3you need to use the next method: =TRANSPOSE(A1:C3). This returns the transpose of the matrix in vary A1:C3.
You too can use VBA (Visible Fundamental for Purposes) to seek out the transpose of a matrix. Right here is an instance of how you are able to do this:
copycodesubtransposeMatrix()
Declare variables for matrices.
dim A(1 to three, 1 to three) as double
Dim B (1 to three, 1 to three) as doublet
Set values for the matrix.
A(1, 1) = 1: A(1, 2) = 2: A(1, 3) = 3
A(2, 1) = 4: A(2, 2) = 5: A(2, 3) = 6
A(3, 1) = 7: A(3, 2) = 8: A(3, 3) = 9
Discover the transpose of the matrix.
For i = 1 to three
For j = 1 to three
B(i, j) = A(j, i)
Subsequent J
Subsequent i
' Output the consequence to the sheet.
For i = 1 to three
For j = 1 to three
cells(i, j). worth = B(i, j);
Subsequent J
Subsequent i
Concluding Sub
This code finds the transpose of the AMatrix and retailer the consequence within the matrix B. The result’s then printed on the sheet.
Observe that this code assumes the matrix is 3×3, however you’ll be able to simply modify it to deal with different sizes by altering the scale of the arrays and the loop boundaries.
Easy methods to multiply a matrix by a scalar (actual quantity).
To multiply a matrix by a scalar (actual quantity), you could multiply every component of the matrix by the scalar. For instance, contemplate the next matrix:
Copy Code A = [ a11 a12 a13 a21 a22 a23 a31 a32 a33 ]
To multiply this matrix by a scalar okyou’ll be able to merely multiply every component of the matrix by ok:
copy code b = [ k*a11 k*a12 k*a13
k*a21 k*a22 k*a23
k*a31 k*a32 k*a33 ]
This can create a brand new matrix that Bis the results of multiplying the matrix by a scalar.Aok
In Excel, you’ll be able to multiply a matrix by a scalar utilizing a method, or you’ll be able to write a customized perform utilizing VBA (Visible Fundamental for Purposes). For instance, to multiply a matrix in a variety by a scalar A1:C3, okYou should utilize the next method: =A1:C3*ok. A1:C3This returns a brand new matrix that’s the results of multiplying the matrix in vary by a scalar ok.
You too can use VBA to multiply a matrix by a scalar. Right here is an instance of how you are able to do this:
Copy Code Sub MultiplyMatrixByScalar()
Declare variables for matrices.
dim A(1 to three, 1 to three) as double
Dim B (1 to three, 1 to three) as doublet
Set values for the matrix.
A(1, 1) = 1: A(1, 2) = 2: A(1, 3) = 3
A(2, 1) = 4: A(2, 2) = 5: A(2, 3) = 6
A(3, 1) = 7: A(3, 2) = 8: A(3, 3) = 9
' Set the scalar.
ok = 2
Multiply the matrix by the scalar.
For i = 1 to three
For j = 1 to three
B(i, j) = ok * A(i, j)
Subsequent J
Subsequent i
' Output the consequence to the sheet.
For i = 1 to three
For j = 1 to three
cells(i, j). worth = B(i, j);
Subsequent J
Subsequent i
Concluding Sub
this code Awill multiply the matrix by a scalar and retailer it okconsequence within the matrix B. The result’s then printed on the sheet.
Observe that this code assumes the matrix is 3×3, however you’ll be able to simply modify it to deal with different sizes by altering the scale of the arrays and the loop boundaries.
Easy methods to discover the inverse of a sq. matrix
The inverse of a sq. matrix is Aa matrix A^(-1)in order that the place is the identification matrix A*A^(-1)=I. IThe identification matrix is a sq. matrix with ones on the diagonal and zeros in all places.
To seek out the inverse of a sq. matrix it’s a must to MINVERSEcan use a perform in Excel. For instance to seek out the inverse of a matrix in a variety A1:C3you need to use the next method: =MINVERSE(A1:C3). This returns the inverse of the matrix in vary A1:C3.
Observe that not all sq. matrices have an inverse. A sq. matrix is invertible if and provided that its determinant is non-zero. The determinant of a matrix is a scalar worth that MDETERMcould be calculated by a method or utilizing a perform in Excel.
You too can use VBA (Visible Fundamental for Purposes) to seek out the inverse of a sq. matrix. Right here is an instance of how you are able to do this:
sub InvertMatrix()
Declare variables for matrices.
dim A(1 to three, 1 to three) as double
Dim B (1 to three, 1 to three) as doublet
Set values for the matrix.
A(1, 1) = 1: A(1, 2) = 2: A(1, 3) = 3
A(2, 1) = 0: A(2, 2) = 4: A(2, 3) = 5
A(3, 1) = 1: A(3, 2) = 0: A(3, 3) = 6
Discover the inverse of the matrix.
B = Software.WorksheetFunction.MINverse(A);
' Output the consequence to the sheet.
For i = 1 to three
For j = 1 to three
cells(i, j). worth = B(i, j);
Subsequent J
Subsequent i
Concluding Sub
This code finds the inverse Aof the matrix and retailer the consequence within the matrix B. The result’s then printed on the sheet.
Observe that this code assumes the matrix is 3×3, however you’ll be able to simply modify it to deal with different sizes by altering the scale of the arrays and the loop boundaries.
MInverseIt’s also vital to notice that the perform will return an error if the matrix doesn’t have an inverse (i.e. its determinant is zero). You should utilize error dealing with methods to cope with this example.
How do you discover the determinant of a sq. matrix?
The determinant of a sq. matrix is a scalar worth that MDETERMcould be calculated by a method or utilizing a perform in Excel. The determinant of a matrix is a measure of the “dimension” of a matrix and is utilized in varied matrix operations resembling B. Discovering the inverse of a matrix.
To find out the sq. matrix, you could MDETERMcan use the perform in Excel. For instance to seek out the determinant of a matrix in a variety A1:C3you need to use the next method: =MDETERM(A1:C3). This returns the determinant of the matrix in vary A1:C3.
You too can use VBA (Visible Fundamental for Purposes) to seek out the determinant of a sq. matrix. Right here is an instance of how you are able to do this:
Copy Code Sub DetermineMatrixDeterminant() Declare variables for matrix and determinant. dim A(1 to three, 1 to three) as double Dhima Date As Double Set values for the matrix. A(1, 1) = 1: A(1, 2) = 2: A(1, 3) = 3 A(2, 1) = 0: A(2, 2) = 4: A(2, 3) = 5 A(3, 1) = 1: A(3, 2) = 0: A(3, 3) = 6 Discover the determinant of the matrix. det = Software.WorksheetFunction.MDeterm(A); ' Output the consequence to the sheet. cells(1, 1). worth = date Concluding Sub
This code finds the determinant of the matrix Aand retailer the lead to a variable det. The result’s then printed on the sheet.
Observe that this code assumes the matrix is 3×3, however you’ll be able to simply modify it to deal with different sizes by altering the scale of the arrays and the loop boundaries.
It is also vital to notice that DetermA perform returns an error if the matrix is not sq.. You should utilize error dealing with methods to cope with this example.