Circular Reference

[MahaRaj®]

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


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

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

Could you please give me any tips.
Thanks.

 

[Myrna Larson]

I answered once, giving you a formula for A6. In re-reading, you want
formulas for both A6 and B6.

A7 and B7 contain the formulas =SUM(A1:A6) and =SUM(B1:B6), and you want
those to be equal.

Basically, in row 6, you want to add a positive number -- a "fudge
factor" -- to bring the total for rows 1:6 up to that in the other column,
if necessary.

In A6 =MAX(SUM(B1:B5)-SUM(A1:A5),0)
In B6 =MAX(SUM(A1:A5)-SUM(B1:B5),0)

Row 8 isn't necessary.

If you don't understand the logic above, for the 1st one, it's the same as

=IF(SUM(B1:B5)>SUM(A1:A5),SUM(B1:B5)-SUM(A1:A5),0)

but the first formula doesn't force Excel to do 4 SUMs rather than 2.

 

[MahaRaj®]

Thanks Guys it sorted now.

<q>
As Paul Corrado advised in microsoft.public.excel.worksheet.functions

Try this in Cell A6. I think it will work

IF(B7>Sum(a1:a5),Sum(a1:a5)-b7,0)
</q>

MahaRaj

 

[MahaRaj®]

Thats great.
using MAX thanks it is more simple. I am going to use this.

Thanks Myrna

MahaRaj