VBA Macro for Solving Linear Equations

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

In article <[email protected]>,
[email protected] says...
> 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)
>

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

--
Regards,
Tom Ogilvy

"monir" <[email protected]> wrote in message
news:[email protected]...
> 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)

Tushar;

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

Regards.


"Tushar Mehta" wrote:

> 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
>
> In article <[email protected]>,
> [email protected] says...
> > 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)
> >
>

Tom;

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

Regards.



"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "monir" <[email protected]> wrote in message
> news:[email protected]...
> > 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)
>
>
>