Simple Circular Reference Problem

[Gary]

This is the first time i am trying to play around with circular reference.
What I am trying to do is.....

I am entering a formula in cell A1, The formula is =A1+B1 (very simple)

I have already checked the Iteration box and set the maximum iterations to
1. (tools/options/calculation)

the formula is working but.....whenever i save it or do anythingin in the
sheet..like..delete something, enter a figure in some other cell etc. etc.,
it adds the last value again.

for example - I enter 2 in B1, A1 shows 2, then I enter 3 in B1, A1 shows 5.
Now, if do anything in the sheet it will add 3 again and A1 would show
8...if I keep on pressing CTRL+S, it keeps on adding 3 to A1.

Can anyone give me the solution to this problem please?

Thanks
Gary

 

[JLatham]

This is one of the reasons that they warn about circular references and why
you have to specifically permit them.

What is happening is that as you do other things on the worksheet, the
Recalculate event is firing and with your formula Excel says to itself "I
must recalculate the formula in A1, so take the current value in A1 and add
whatever is in B1 to it" and so you have the ever increasing value in A1
every time Recalculate fires off.

You have to be very cautious with circular references although there are
places where they will work for you, although there's usually another way to
achieve the same end.

Consider this - you run a business and have costs for an item in column A,
the amount of profit to add to it to get a selling price in B and that total
in C. But then you realize you have to pay a fee back on items purchased via
credit card and you realize that's going to eat into your profit, so you want
to reprice so you get to keep your profit. This can be solved with a
circular reference like this:
A1: = 99 (your cost)
B1: = 15 (your desired profit)
C1: = A1+B1 (basic sell price)
D1: = C1+E1 (about to become a circular reference - your final sell price)
E1: = C1 * .04 (presumes 4% kickback to the credit card company)

This creates a circular reference between D1 and E1. But you won't see
changes during the recalculate event because the value in C1 is fixed at the
sum of A1 and B1. But if I'd just put a value into C1 as you did with A1,
then I'd have the same problem.

I hope this helps you understand circular references a little more and gives
you an insight into when you may be able to get away with using them.

 

[JLatham]

Forgot - the non-circular method to solve the same problem:
A1: = 99
B1: = 15
C1: = A1+B1
D1: =C1*1+(0.04 / (1-0.04))