| Home |
| Search |
| Today's Posts |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| adding on to an existing total & avoiding a circular reference blo | Excel Discussion (Misc queries) | |||
| Ho do I do this without a circular reference? | Excel Discussion (Misc queries) | |||
| Circular Reference??? | New Users to Excel | |||
| Macro avoiding circular reference | Excel Worksheet Functions | |||
| Avoiding circular reference on formula | Excel Discussion (Misc queries) | |||
Threaded Mode
