VBA Macro for Solving Linear Equations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello;

I've a number of sets of simultameous linear equations, with the number of
unknowns and no. of equations are equal in each set.
All the coefficients are to be read from a w/s. The values of the unknowns
are to be posted in a different w/s in the same w/b.
The number of unknowns in each set is less than 10. (In other words, not in
the 100s or the 1000s)

1. I'm a bit surprised that such relatively simple math. tool is not
included as a built=in feature in the Excel Analysis Tool. or, is it ?

2. It is likely that several VBA macros (w/code) do exist, but I've tried
100s of web sites and could not find what I'm looking for!

Could you please advise or refer me to a particular site ?

3. Alternatively, I do have a well-commented and very reliable (LU
Decomposition) FORTRAN Subroutine code (about 40 relatively simple
statements) that could possibly be converted (with your help!) to a VBA macro
for use in Excel (2003 Win XP).

I would very much appreciate your suggestion(s).

Thank you kindly.

(PS. I've already solved couple of sets manually, which could be used later
to validate the macro)
 
If you have the equations in matrix form, AX=B, then the solution is
X=A-inverse * B. XL supports matrix multiplication and inverse
operations. Suppose you have the A values in a range, say F2:G3 and
the B values in a column, say H2:H3. Then, select the appropriate
number of cells (2 in this case) in a column and array enter
=MMULT(MINVERSE(F2:G3),H2:H3)

--
An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, XL will display curly brackets { and }
around the formula

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Alan Beban posted previously:

Given
a11X1+a12X2+a13X3=c11
a21X1+a22X2+a23X3=c21
a31X1+a32X2+a33X3=c31

in which the unknowns are X1, X2 and X3; the coefficients of the
unknowns are
a11,a12,a13,a21,a22,a23,a31,a32,a33; and the constants are c1, c2 and
c3.

In Cells A1:C3 insert, respectively,
a11,a12,a13,a21,a22,a23,a31,a32,a33; i.e., this is the matrix of the
coefficients
In Cells D1:D3 insert respectively, c11,c21,c31; i.e., this is the
matrix of the constants

Array enter (i.e., enter with Ctrl+Shift+Enter instead of just Enter)
into E1:E3

=MMULT(MINVERSE(A1:C3,D1:D3))

The result , in E1:E3, is the matrix of the solutions, the values of X1,
X2, X3, also called the Solution Vector

e.g.,

1X1+2X2+3X3=16
5X1+2X2+6X3=36
1X1-1X2+2X3=9

With 1,2,3 in A1:C1; 5,2,6 in A2:C2; 1,-1,2 in A3:C3; and 16,36,9 in
D1:D3

the formula will return 2,1,4 to E1:E3; i.e., X1=2, X2=1, X3=4

In Matrix form, the basic equation, with A, X and C being matrices, is
equivalent to

AX=C

Multiplying both sides by the inverse of A from the left gives

(Ainverse)AX=(Ainverse)C which, because (Ainverse)A=1, reduces to
X=(Ainverse)C

The above Excel formula simply says return the result of (Ainverse)C,
i.e., X

Alan Beban
 
Tushar;

Thank you very much. Your response was very helpful. Greatly appreciared.

Regards.
 
Hello;

Could someone please advise on the link to:
"MS FORTRAN DISCUSSION GROUP" site ??

I had used it in the past, but could not locate it anymore!!
Thank you kindly.
 
Back
Top