Circular Reference

[chemeng123]

Hey guys,

I'm new here and I had a few questions regarding circular referencing. Yesterday I opened up my excel and just typed in three random simultaneous equations and excel automatically detected circular referencing. So I clicked ok on the pop-up and went into my options to turn on iterations. Bearing in mind this was my first time, excel did not find the solutions. I'm not quite sure whether when I turn on the iterations excel finds the solution automatically or I need to first input an initial guess? My equations were:
x= z/y
y=0.5*x+2
z=y^2-3
Basically I entered the values in the a and b column, so my excel screen looked a bit like this:
| x= | B3/B2 |
| y= | 0.5*B1+2 |
| y= | B2^2-3 |

I know for a fact that I'm supposed to get x = 2, y = 3 and z = 6 but all excel is giving me is error divby0.

Would really appreciate some help regarding this issue. Also I am quite new to excel so if it turns out the solution to this problem is quite lengthy could you please be detailed?

Thanks in advance

[Dennis7849]

It would help to attached a sample workbook.

[MrShorty]

Just setting up the circular reference is not going to tell Excel how to solve the equations. you as the programmer have to understand the algorithms to solve the system of equations before you can program them into Excel.

It's been a while, but, if I remember right, the usual, general approach to solving systems of equations involves setting up a matrix to represent the equations, then taking the inverse of that matrix to find the solution (if any) to the system of equations. Excel has built in matrix functions (see MINVERSE and MMULT as examples) that could be useful for this kind of solution.

As for solving the specific system given, I solved for z and x as functions of y, which gave me a single function of y (0=-y^2+4y-3). Use the quadratic equation to find the roots of this equation. Then plug those values for y back into z and x. So there appears to be two solutions to this system of equations. one for y=3 as you noted, and another solution for y=1.