Circular Reference in Excel

[Axel]

Hi All.

 

I'll try to explain this as best I can

   A       B 
1 Add      50 <----User Editable
2 Add      70 <----User Editable
3 Sub      20 <----4% of Total in B7 (Formula)
4 SbTotal 100 <----B1+B2-B3 (Formula)
5 Add     300 <----User Editable
6 Add     100 <----User Editable
7 Total   500 <----B4+B5+B6 (Formula)

The figure calculated in B3 is calculated as set percentage of B7.  Understandably this produces a circular reference whereby I imagine some horrible never ending loop would occur.

 

I wondered if there was a way to loop this until the Total in B7 stopped changing. 

 

These are my thoughts in pseudo-code:

 

LOOP UNTIL VAR1 = VAR2
 
    RUN FORMULA IN CELL B7
    VAR1 = B7
    RUN FORMULA IN CELL B4
    RUN FORMULA IN CELL B7
    VAR2 = B7
 
END LOOP

 
 

They ultimately only need to match to two decimal spaces.

 

 

Many thanks!

[Nick H. Supervisor]

Interesting, myself and Haggis were working through a similar issue not too long ago. It should be possible by creating a macro/VBA script, but for some reason in my testing it always seemed temperamental and I never made it past that.

[Lant]

If you can express this as a constraints satisfaction problem you might be able to use excel to solve it.

 

There's loads of references on how to do this online:
http://www.wikihow.com/Use-Solver-in-Microsoft-Excel

[ShadeOfBlue]

I think this is a case of just getting so caught up in doing this in a very spreadsheet-centric way, that you've missed the forest for the trees. This is just algebra.

Let..
A,B,C,D = the 4 cells added
X = the cell subtracted
T = the total

Therefore..
A+B-X+C+D = T
X = 0.04T

Then..
A+B+C+D = T+0.04T
A+B+C+D = 1.04T
(A+B+C+D)/1.04 = T

Or, in other words: Total = (B1+B2+B5+B6)/1.04

Done.

Alternatively, you could replace the one for the amount subtracted.

X = 0.04(A+B-X+C+D)
X = 0.04(A+B+C+D) - 0.04X
1.04X = 0.04(A+B+C+D)
X = 0.04(A+B+C+D)/1.04
X = (A+B+C+D)/26

Subtracted amount = (B1+B2+B5+B6)/26

Replace one or both of those two formulas, and the circular reference is gone.