Basic circular reference problem

Hi Guys,

would really appreciate some help on a circular problem - afraid I am a bit of an amateur. I have to have allow circular turned on at the moment but I know that is not ideal. Having looked up this issue most people seem to solve it through algebra but afraid I cant quite get my head around it.

I am trying to get D14 to calculate a percentage of D8, however D8 is summing D14 as part of its calculation and is therefore circular.

Anyway round this?

Chris

What formula? Where? How can we see what's happening from a pretty picture of a spreadsheet????


Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

As Glenn says, it is difficult to be specific without the formulas you are using. Typically, this algebra problem is one where the x is on both sides of the equation, but still should be easy to solve algebraically.

x=rate*(sum(other stuff)+x) then solve for x.

Here's a tutorial I recommend: http://www.purplemath.com/modules/solvelin3.htm Note that there are several examples where x is on both sides of the equals sign, and the first step is to usually move all of the x's to the same side.

As I suspected, it is the basic algebra problem I mentioned above:

Debt=Sum(fees)+arrangefee-equity -- arrangefee is separated from the rest of the fees to better see it in the algebra problem.
arrangefee=debt*rate -- substitute
debt=sum(fees)+debt*rate-equite OR arrangefee/rate=sum(fees)+arrangefee-equity

Either solve the first one for debt, enter that formula into the debt cell, and the arrangefee cell stays unchanged, or solve the second for arrangefee, put that into the arrangefee cell, and the debt cell formula remains unchanged.

Can I assume you can handle the algebra problem?

No, that doesn't alleviate the circular reference problem. I had not even noticed that you had a duplicate debt cell down in row 33.

Perhaps before even looking at the spreadsheet/programming problem, could you solve either of those equations on paper? Could you use those to solve one case on paper? I am a firm believer that one needs to understand the math/algebra behind a problem before programming it into the spreadsheet.

If we go with the first "debt" equation, then we have (substituting cell references for variables)
D8=sum(D10:D13)+D35*D8-D32 (recall that D35*D8 is the formula in D14).

The end goal here is to solve this equation for D8. I am not, yet, going to do the algebra for you (anyone who is managing this much money ought to be able to handle this simple algebra problem, IMO).

If it helps, substituting numbers from your example in:
x=5.351E6+0.02*x-5E6

I kind of hate to be stubborn about making you solve this yourself, but, again, this is pretty basic algebra that anyone beyond secondary should be able to solve. Does that help you see the problem a little more clearly?