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.

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

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
>
>

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.
>
>
>
>