Help avoiding a circular reference
 

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.

Help avoiding a circular reference
 

"Ted Metro" wrote...
....
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.

Help avoiding a circular reference
 

Worked like a charm Harlan. Well played sir!

"Harlan Grove" wrote:

"Ted Metro" wrote...
....
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.