Help for formula.

G

George

Hi.
I want to solve a linear of n X n equations system in Excel, where the
solution can be derived as follow:

Xn = ao
Xn-1 = a1 * Xn
Xn-2 = a2 * Xn-1 + a3 * Xn
Xn-3 = a4* Xn-2 + a5 * Xn-1 + a6 * Xn
e.g.
The an coefficients (a0,a1,a2,….an) are all known.
Lets simplify the above system using an example. Lets say that n=5 and:
X5=7
X4=2*X5
X3=3*X4+2*X5
X2=1*X3+3*X4+4*X5
X1=1*x2+1*x3+1*x4+2*x5
(Then the solution is: X1=210, X2=126, X3=56, X4=14 and X5=7)
Any idea how can I solve these n x n equations in general form in Excel?
(I tried with sumproduct() but I stuck in coefficients of an terms.)
Thank you.
 
B

Bernard Liengme

Hi George
Not sure this is actually a problem in 'solving' equations or just computing
values. Never mind, here is one way to get your answers
a0 a1 a2 a3 a4 a5 a6 a7 a8 a9 a10
7 2 3 2 1 3 4 1 1 1 2

x1 x2 x3 x4 x5210 126 56 14 7

x5 0 7x4 1 1 5 14
x3 2 3 4 56x2 4 6 3 126
x1 7 10 2 210Rows1 and 3: labels and values of coefficients
Row 3 - blank
Rows 4 and 5: labels and formula: in A5 =E11, in B5 =E10, etc
Call this the X array
(I wanted to work vertically and horizontally so these are duplicate values)
Row 10 - blank
In Rows 7 thru 11label;
2 index values for the coefficient array and 1 for the X array; and formulas
as shown
In B7 = A2
In B8 a somewhat long formula
=SUMPRODUCT((INDEX($B$2:$K$2,B8):INDEX($B$2:$K$2,C8)),(INDEX($A$5:$E$5,D8):INDEX($A$5:$E$5,5)))
this is copied down to B11 and gives the required results
I expect we could tidy this up a bit but it gives you a start
Send me private email (addy from my website) and I will send you a file -
what version of Excel do you use?
best wishes
 
G

George

Thank you both for your help
but as I said the 5 X5 is only an example to describe the problem. I am
looking for a general solution in all cases either the system is 5 X 5 or is
40 X 40 or 150 X 150 e.g.
I use Excel 2007.
 
B

Bernard Liengme

What is the maximum value of N?
Bernard

George said:
Thank you both for your help
but as I said the 5 X5 is only an example to describe the problem. I am
looking for a general solution in all cases either the system is 5 X 5 or
is
40 X 40 or 150 X 150 e.g.
I use Excel 2007.
 
L

Lori Miller

If you gather all the x terms on the left hand side of the equations and
constants on the right, your can write it in matrix form Ax=b, so the
solution for x is the inverse of A multiplied by b. In this example:

=MMULT(MINVERSE({0,0,0,0,1;0,0,0,1,-2;0,0,1,-3,-2;0,1,-1,-3,-4;1,-1,-1,-1,-2}),{7;0;0;0;0})

A is the first array and b is the second giving x={210;126;56;14;7}.

For larger arrays, create one column for each coefficient of A and one for
the constant term b. Remember to change the sign of coefficients on the
right hand side.
 
G

George

Thank you Lori, but the same problem remains. The N is variable up to 200.
Imagine a system of 200 linear equations. You have to create all the Dx1/D,
Dx2/D,……Dx200/D, in order to calculate x1,x2,…x200. Also you ‘spread’ a lot
of formulas in all the place of the the worksheet to ‘catch’ all cases.
But I spend all the last night and I think I found a simple formula, which is:

SUMPRODUCT(OFFSET($D$6;E9;0):OFFSET(OFFSET($D$6;E9;0);B9;0);$G$5:G9)
The formula calculates the xi backwards using the previous cell solution.
The term $G$5:G9 are the xi solution.
The term OFFSET($D$6;E9;0):OFFSET(OFFSET($D$6;E9;0);B9;0) increase in every
step (that means in every cell) the number of coefficients that demanded.

So we only need to drag down the formula.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top