Ho do I do this without a circular reference?

[mmednick]

I start with a collumn of financial data. The value of the last row is
the sum of all the other rows. If this number is negative however, I
want the value of the number in the first row to update by adding the
absolute value of the last row figure to itself.

A 100 <-- I want this number to recalculate with the absolute value of
E
B 50
C 50
D-250
E -50 <-- sum of rows A through D

I know this is troublesome but I think I saw somewhere a colleague used
a function to copy out values to other cells in order to break the
circular refernce in a problem like this.

Anyone have any suggestions?
the n00bish

 

[mmednick]

bump

pleas

 

[mmednick]

Help me im sinking, they've sunk my battleship

 

[poloboyUK]

You can't get round the circular ref. Sorry

 

[Herbert Seidenberg]

If what you want to do is prevent the sum from going negative
by adding to A1, you can do this:
Select the negative sum.
Tools > Goal Seek
To Value: 0
By changing cell: A1

 

[Bill Martin]

You can sort of do it with a second column:

[A1]=10 [A2]=if(a5>=0,a1,a1-a5)
[B1]=50 [B2]=b1
[C1]=50 [C2]=c1
[D1]=-250 [D2]=d1
[E1]=sum(a1:d1) [E2]=sum(a2:d2)

Not precisely what you want, but perhaps close enough to be made to work.

Bill

 

[vezerid]

mmednick

I am not sure if this is what you want exactly, but the following might
be close.

Use an additional cell (say C1) to store the original value that you
want for A. (this is probably the copy you heard about). In your
example set it to 100.

Use yet another cell (say E1) to enter 0.

Input all other values (B-D).

Enter as formula in A5: =SUM(A1:A4)

Tools | Options | Calculation. Check the Iterations checkbox (this now
allows self reference). Set Number of Iterations to 1.

In A1 enter:
=IF(E1=0,C1,IF(A5<0,C1+ABS(A4),C1))

Now you are ready to go to cell E1 and enter the value 1. This will
activate the nested IF() in the above formula, which will either add or
leave the number as it is entered in the copy cell C1.

Does this help?
Kostis Vezerides