Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference... Help!
Hi All,
I have an issue with a circular reference that I need some help on, let me take sometime to explain my problem...... I have a set of values like thus..... A B (calc) B(result) C(calc) C(result) 1 58.02285714 =A1+A2 -0.0214 =C2-B1 58.3414 -circ ref C1,C2 2 58.00142857 =A2+A3 -0.0214 =C1+B1 58.3200 3 57.98 =A3+A4 -0.0229 =C2+B2 58.2986 4 57.95714286 =A4+A5 -0.0229 =C3+B3 58.2757 5 57.93428571 0 0 =C4+B4 58.2529 I get a circular reference between C1 and C2, as I would expect. I have turned on the Iterations and set it to 1, which solves the circ ref problem, but the trouble is that the values in A1:A5 (which are derived from another set of values) can change at any time, so C1 or C2 will not recalc when this happens, as iterations is on. Setting it to anything above 1 does not solve my problem. I am setting up a spreadsheet similar to the example above for business users who have minimal knowledge of excel, so I am looking for the most simplest solution. At present, users will manual type one the values in C1:C5 to avoid a circular reference. Is there anyway of getting around this so it is automated? i.e. using VBA to influence the calculation process. Forgive me if you do not understand what I am asking for, please say so, and I will try to shed more light on it, but I have tried to simplify the example as far as possible from something that is a very complex business model. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference... Help!
Encolose your circular reference formulas in an IF(). Use another cell,
which will hold an initialization value (0 or 1). Say K1. =IF(K1=0, 0, calculation). This solves the problem of recalculating at will but it will have the (possibly) unpleasant effect of displaying 0 at all other times. HTH Kostis Vezerides |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference... Help!
Hi Kostis,
My simplified example is already closed within about 5 IF statements that do other things. But in the simplest sense, your suggestion just returns a zero value in cell C1, and all other cells (C2:C5 in my example) calcualate off this zero value. Thanks for the suggestion though, any other ideas? I have tried various things but to no evail. Thanks, Bhupinder. "vezerid" wrote: Encolose your circular reference formulas in an IF(). Use another cell, which will hold an initialization value (0 or 1). Say K1. =IF(K1=0, 0, calculation). This solves the problem of recalculating at will but it will have the (possibly) unpleasant effect of displaying 0 at all other times. HTH Kostis Vezerides |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference... Help!
I have had an idea...
In my example the differences between A1, C1; A2, C2; A3, C3 etc... are always the same, (in this case its -0.3186). Is there anyway I can find out this difference just by using columns A and B, then I can use it to derive column C? Any help at all will be very much appreciated... Thanks, Bhupinder "Bhupinder Rayat" wrote: Hi All, I have an issue with a circular reference that I need some help on, let me take sometime to explain my problem...... I have a set of values like thus..... A B (calc) B(result) C(calc) C(result) 1 58.02285714 =A1+A2 -0.0214 =C2-B1 58.3414 -circ ref C1,C2 2 58.00142857 =A2+A3 -0.0214 =C1+B1 58.3200 3 57.98 =A3+A4 -0.0229 =C2+B2 58.2986 4 57.95714286 =A4+A5 -0.0229 =C3+B3 58.2757 5 57.93428571 0 0 =C4+B4 58.2529 I get a circular reference between C1 and C2, as I would expect. I have turned on the Iterations and set it to 1, which solves the circ ref problem, but the trouble is that the values in A1:A5 (which are derived from another set of values) can change at any time, so C1 or C2 will not recalc when this happens, as iterations is on. Setting it to anything above 1 does not solve my problem. I am setting up a spreadsheet similar to the example above for business users who have minimal knowledge of excel, so I am looking for the most simplest solution. At present, users will manual type one the values in C1:C5 to avoid a circular reference. Is there anyway of getting around this so it is automated? i.e. using VBA to influence the calculation process. Forgive me if you do not understand what I am asking for, please say so, and I will try to shed more light on it, but I have tried to simplify the example as far as possible from something that is a very complex business model. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I avoid circular reference AND extra input | New Users to Excel | |||
Iterate Circular Reference | Excel Discussion (Misc queries) | |||
Highest Value / Circular Reference Help | Excel Discussion (Misc queries) | |||
Highest Value / Circular Reference Help | Excel Worksheet Functions | |||
Help solve a Circular Reference | Excel Worksheet Functions |