Circular Reference
 

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(B7A78,A8,0) and in B6. IF(B7A78,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.

Circular Reference
 

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®" wrote in message
...
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(B7A78,A8,0) and in B6. IF(B7A78,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.

Circular Reference
 

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(B7Sum(a1:a5),Sum(a1:a5)-b7,0)
</q

MahaRaj



"MahaRaj®" wrote in message
...
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(B7A78,A8,0) and in B6. IF(B7A78,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.

Circular Reference
 

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

Thanks Myrna

MahaRaj

"Myrna Larson" wrote in message
...
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®" wrote in message
...
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(B7A78,A8,0) and in B6. IF(B7A78,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.