Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Macro for Solving Linear Equations
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" wrote in message ... 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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Macro for Solving Linear Equations
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 , 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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Macro for Solving Linear Equations
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" wrote in message ... 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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Macro for Solving Linear Equations
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. "monir" wrote: 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" wrote in message ... 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Solving multiple equations | Excel Worksheet Functions | |||
Solving coefficients of Polynomial Equations by Macro | Excel Discussion (Misc queries) | |||
solving of linear equation | Excel Discussion (Misc queries) | |||
Solving sets of equations | Excel Worksheet Functions | |||
Solving equations programmatically | Excel Programming |