Help avoiding a circular reference

G

Guest

This is almost a logic problem, but I can't figure out how to tackle it in
Excel.

I have a source of funds list in columns A & B

State Funds 1,000
Tax Credits 5,000
Cash 8,000
Financing sum(d1:d4)-sum(b1:b4)
Total sum(b1:b4)

and a use of funds list in columns C & D

construction 2,000
Fixed Assets 12,000
Soft Costs 2,000
Financing Costs .02*b4
Total sum(d1:d4)

The financing cell in b4 is essentially the amount of money that needs to be
spent minus the available cash. This amount will be financed.

The problem is that d4 (the financing costs) is dependent upon the amount
needed, which is dependent upon d4.

Is there an easier way to display this? It's an existing sheet, and people
don't want hardcoded numbers, they want it dependent upon the amount needed.
 
H

Harlan Grove

Ted Metro said:
I have a source of funds list in columns A & B

State Funds 1,000
Tax Credits 5,000
Cash 8,000
Financing sum(d1:d4)-sum(b1:b4)
Total sum(b1:b4)

So B4 contains a formula that refers to itself?
and a use of funds list in columns C & D

construction 2,000
Fixed Assets 12,000
Soft Costs 2,000
Financing Costs .02*b4
Total sum(d1:d4) ....
The problem is that d4 (the financing costs) is dependent upon the amount
needed, which is dependent upon d4.
....

Time for a little algebra.

B4 = SUM(D1:D4) - SUM(B1:B4)
= SUM(D1:D3) + D4 - SUM(B1:B3) - B4
= SUM(D1:D3) + 0.02 * B4 - SUM(B1:B3) - B4
= SUM(D1:D3) - SUM(B1:B3) - 0.98 * B4
1.98 * B4 = SUM(D1:D3) - SUM(B1:B3)

B4: =(SUM(D1:D3)-SUM(B1:B3))/1.98

or if the name Rate referred to 0.02,

B4: =(SUM(D1:D3)-SUM(B1:B3))/(2-Rate)

You can then check this in another cell using the formula

=ABS(D5-B5-B4)<1E-6

which ignores rounding error with magnitude less than 0.000001.
 
G

Guest

Worked like a charm Harlan. Well played sir!

Harlan Grove said:
So B4 contains a formula that refers to itself?

....

Time for a little algebra.

B4 = SUM(D1:D4) - SUM(B1:B4)
= SUM(D1:D3) + D4 - SUM(B1:B3) - B4
= SUM(D1:D3) + 0.02 * B4 - SUM(B1:B3) - B4
= SUM(D1:D3) - SUM(B1:B3) - 0.98 * B4
1.98 * B4 = SUM(D1:D3) - SUM(B1:B3)

B4: =(SUM(D1:D3)-SUM(B1:B3))/1.98

or if the name Rate referred to 0.02,

B4: =(SUM(D1:D3)-SUM(B1:B3))/(2-Rate)

You can then check this in another cell using the formula

=ABS(D5-B5-B4)<1E-6

which ignores rounding error with magnitude less than 0.000001.
 

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