Circular Reference

[MahaRaj®]

HI
I am making a Trial Balance. and getting a circular ref in a table as below.


.................A.................B
1.... .........15...............16
2 .............15...............17
3..............13...............13
4..............13...............33
5..............22...............33
6 ..IF(B7>A7,A8,0) I have put a formula in A6.
7 Totals....78.............112
8 Diff.......34 (=a7-b7)

So the totals should be 112 in both cells (A8 and B8)

I do not know how to get rid of circular reference. Do I need a simle
formula or VB code?
I am sure there would be a way. I tried Iteration thingy but it still not
working.


Could you please give me any tips.
Thanks.


MahaRaj


P.S(I also posted it to excel.programming group but no response yet. thanks)

 

[Paul Corrado]

Try this in Cell A6. I think it will work

IF(B7>Sum(a1:a6),Sum(a1:a6)-b7,0)

 

[Myrna Larson]

In your example, the totals are in row 7; in your explanation you talk about
the totals being in A8 and B8. I'll assume that row 7 is correct.

You want the total in A7 to be the same as that in B7, and you want to achieve
that by changing the value in A6. Seems to me the formula for A6 should be

=B7-SUM(A1:A5)

 

[MahaRaj®]

Thanks Paul for your excelent idea.
It sorted. I the logic was not to get figure from the totals (from the
bottom) but from the top as Sum(range).

I really appreciate it. I am glad I came to the right place.

Thanks

 

[MahaRaj®]

Thanks Myrna.
I got the idea from Paul, where I was wrong? and I got it now.
Thanks for your words.
MahaRaj