Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Circular Reference | Excel Worksheet Functions | |||
Circular reference | Excel Discussion (Misc queries) | |||
circular reference when using a UDF | Excel Discussion (Misc queries) | |||
Circular reference | Excel Discussion (Misc queries) | |||
Circular reference | Excel Programming |