Solve mathematical problem

As with a lot of problems, I think it is easiest to first understand how to solve the problem mathematically, then it is much easier to solve in Excel (or whatever programming language you choose to use). With that goal in mind, I would first suggest you review this review of methods for solving systems of linear equations: http://www.purplemath.com/modules/systlin1.htm

I tend to use either substitution or Gaussian elimination. I don't know how rusty your algebra is. If it is quite rust, I would probably suggest you refamiliarize yourself with how to use substitution to solve a system of equations. If you remember Gaussian elimination (in particular, how to express this kind of problem in terms of matrix algebra), then I would suggest using Excel's built in matrix manipulation functions (MMULT, MINVERSE, etc.) to find the solution to your system of equations.

maybe just fill out the formula in cells in a spreadsheet and use solver/goal seek.

this was pretty quick for me on this problem.
I just typed out 5 cells put the formulae in whatever cells I could and ran the solver.

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	0	0	1	0	1		x		14		x		7	L1:L5:{=MMULT(MINVERSE(A1:E5), I1:I5)}
2	0	-1	0	1	0		y		1		y		4
3	1	-2	0	0	0	*	z	=	-1		z	=	6
4	0	1	1	0	0		t		10		t		5
5	1	1	1	1	1		u		30		u		8

I converted all your formula into a set of cells linking to each other. to make it easy they are nominally formula for x y z and u and one cell for t (which could easily have been u or any other letter i guess) which I give the value of 1.
and the last formula is my total formula I plan to use for the goal seek so i write that formula down as = sum of the cells with the other formula.

now go to data in the ribbon
select what if analysis and goalseek or select solver both are reasonably self explanatory but goalseek is faster to explain it has
"set cell" "to" "by changing"

set cell "the one with the total", to "30" by changing "the cell that you left with no formula".

Sorry about my poor explanation but give it a go - it is easier than I make it sound

Coefficients:

0*x + 0*y + 1*z + 0*t + 1*u = 14
0*x + -1*y + 0*z + 1*t + 0*u = 1
1*x + -2*y + 0*z + 0*t + 0*u = -1
0*x + 1*y + 1*z + 0*t + 0*u = 10
1*x + 1*y + 1*z + 1*t + 1*u = 30

Originally Posted by YasserKhalil

I have five values [....]
z + u =14
t = y + 1
x = 2y-1
y + z = 10
x + y + z + t +u =30
How to find these values using Excel?

Or you could do it the easy way. ;-)

Most of the formulas translate directly into Excel, some requiring minor algebraic manipulation, to wit:

U1: =14-Z1
T1: =Y1+1
X1: =2*Y1-1
Z1: =10-Y1
Y1: =(30-14)/4
A1: =X1+Y1+Z1+T1+U1

The formula in Y1 is derived by simple substitution and minor algebraic manipulation, namely:

x + y + z + t + u = 30
(2y-1) + y + (10-y) + (y+1) + (14-(10-y)) = 30
4y + 14 = 30
y = (30 - 14)/4

this is for Mr. karedog .. he's professional at that

??????????????????

well is suppose mr karedog will just happen to stumble across this thread and come back with an good idea,
but i suspect he /she does not search the board for a mention of his/her name

Hi Yasser,

This is very easy to achieve using Excel, since Excel already has all the needed functions to do this (MINVERSE(), MMULT(), as pointed by MrShorty in #2).

To learn the manual way to do this, please look at this website :
http://www.mathsisfun.com/algebra/sy...-matrices.html

First, we need to convert the formulas into a form that all variables are located left of the equal sign, and the constant is located right of the equal sign.

So your equations :

z + u =14
t = y + 1
x = 2y-1
y + z = 10
x + y + z + t + u =30

need change to this form :

z + u = 14
t - y = 1
x -2y = -1
y + z = 10
x + y + z + t + u = 30

Then input this values to Excel like the screenshot below :
EquationSolver.png

This is the code :

Please Login or Register  to view this content.

Run this macro, enter A2:F6 as the range, and the result will be shown.

Regards

You are welcome, glad I can help.

Regards

After, shamelessly copying from Karedog, I've come up with this code for shg's setup:

Please Login or Register  to view this content.

Hi Yasser,

No, I don't believe that you could go from the original post to the final result without first doing the preparation described in karedog's first post. - Sorry

Hi Yasser,

I wrote a routine for your problem - just paste the equations from A10

Please Login or Register  to view this content.

*How do you want to handle the situations with no solution??

Hi Yasser,

I've thought of a few improvements overnight - will attach when completed - but, as is, the constant term must be the last term on the right side of the equation.

That's what I'm trying to fix now. Even if successful the equations must have just one term for each variable and one term for the constant. And, it's possible to write equations that have no solution - right now, if there is no solution, the program just stops.

Hi Yasser,

Here's the code:

Please Login or Register  to view this content.

And, here's the file:

You're welcome and thanks for the rep!

With this one you can just paste the equations from A1:

Please Login or Register  to view this content.

You're welcome and thanks for the compliment

*Change the third line to:

Please Login or Register  to view this content.

A correction:

Please Login or Register  to view this content.

Hi Yasser,

Still another correction - sorry

Please Login or Register  to view this content.