View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default How do I calculate simultaneous equations?

"Niek Otten" wrote...
Can probably done using Solver. I'm more used to Goal seek, so I
rework to one variable

I use cell A2 for x and B2 for y.

Reworking 21x+6y=10:
x = (10-6y)/21
So the formula in A2 is:
=(10-6*B2)/21

....

Not enough algebra. This can be completely solved analytically. Just
substitute your expression for x based on the first equation into the
second equation. First simplify: 6y appears in both equations, so
replace it with z = 6y.

21x + z = 10
(7x - 20)^2 + (z + 10)^2 = 200

z = 10 - 21x

Then substitute the RHS for z in the second equation.

(7x - 20)^2 + (10 - 21x + 10)^2 = 200
(7x - 20)^2 + (20 - 21x)^2 = 200
49x^2 - 280x + 400 + 400 - 840x + 441x^2 = 200
490x^2 - 1120x + 800 = 200
49x^2 - 112x + 60 = 0

So x has 2 solutions

56/49 + (3136/2401 - 60/49)^0.5 and 56/49 - (3136/2401 - 60/49)^0.5

and y also has two solutions. Solver will only find one, depending on
the initial state of the variable cell. Also, the Solver solution is
approximate while the analytical solution is accurate to machine
precision. Finally, as any who uses numerical analysis in their job
will tell you, if all it takes is 10 or fewer lines of algebra to
reach an exact solution, that ALWAYS preferable to ANY iterative
approach.