circular reference (Excel 2000)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

peaspud - 21 Nov 2005 10:00 GMT

Hi, I need help.I'm sure there is a simple answer,but i can't seem to find
it??!! All i'm trying to do is this:
A1 A8
25 25
Simple? I need the total (A8) to add the input number (A1), even if i change
the input number.But all i seem to get is a "circular reference"
comment.There must be a formula out there that can help me??

Reply to this Message

Ian - 21 Nov 2005 10:16 GMT

Do you mean you need A8 to equal A1 (as your example suggests)? In A8 enter
=A1 to do this.
Do you mean you need a add A1 to whatever is already in A8? In A8 enter
=A1+A8 to do this. This will result in a circular reference. Go to
Tools>Options>Calculation, select Iteration and change Maximum iterations to
1. The problem with this is that every time the sheet is recalculated, A1
will again be added to A8. There are ways round this using code, but there's
no point in going into this unless this is really what you need.

Signature

Ian
--

> Hi, I need help.I'm sure there is a simple answer,but i can't seem to find
> it??!! All i'm trying to do is this:
[quoted text clipped - 4 lines]
> the input number.But all i seem to get is a "circular reference"
> comment.There must be a formula out there that can help me??

Reply to this Message

peaspud - 21 Nov 2005 10:51 GMT

Thank you for your reply.However it did'nt seem to help me, not your fault.I
think i need to explain it better.I need A8 to add together each input number
i put in A1.Even if i delete the A1 number to put in another, i need A8 to
give me a total of each number i've put in and deleted.Does that make
sense???...Hope you can help i'ts driving me mad!!

> Do you mean you need A8 to equal A1 (as your example suggests)? In A8 enter
> =A1 to do this.
[quoted text clipped - 13 lines]
> > the input number.But all i seem to get is a "circular reference"
> > comment.There must be a formula out there that can help me??

Reply to this Message

Bob Phillips - 21 Nov 2005 11:53 GMT

Could you possibly mean just putting =A1 in A8?

Signature

HTH

RP
(remove nothere from the email address if mailing direct)

> Thank you for your reply.However it did'nt seem to help me, not your fault.I
> think i need to explain it better.I need A8 to add together each input number
[quoted text clipped - 19 lines]
> > > the input number.But all i seem to get is a "circular reference"
> > > comment.There must be a formula out there that can help me??

Reply to this Message

Niek Otten - 21 Nov 2005 12:00 GMT

Look here:
http://www.mcgimpsey.com/excel/accumulator.html

Signature

Kind regards,

Niek Otten

Reply to this Message

peaspud - 21 Nov 2005 18:16 GMT

Thank you all for your help.It seems to have done the trick.Problem is ,now i
have the answer, im not sure if i was doing it right in the first place.??
I shall try out the formula i have now and see if it works ok for what i
wish to do,
if it does not work i hope i can ask for all your help again???
Thanks once again

> Look here:
> http://www.mcgimpsey.com/excel/accumulator.html
[quoted text clipped - 7 lines]
> > the input number.But all i seem to get is a "circular reference"
> > comment.There must be a formula out there that can help me??

Reply to this Message

vezerid - 21 Nov 2005 18:02 GMT

peaspud Wrote:

> Hi, I need help.I'm sure there is a simple answer,but i can't seem to
> find
[quoted text clipped - 5 lines]
> the input number.But all i seem to get is a "circular reference"
> comment.There must be a formula out there that can help me??

For your re-reply I understand that you want A8 to contain the
cumulative sum of all numbers entered in A1. This can be done in the
following manner:

1. Use another (say K1) which will contain the number 0 or 1. This will
be used to initialize your calculations. Initially enter 0.

2. Go to Tools|Options|Calculation tab and check Iterations and set
Maximum Iterations to 1.

3. In A8 put the formula =IF(K1=0, 0, A8+A1)

This last formula will initially show 0. You can enter your first
number in A1. Then you set K1 to 1. As long as K1 is 1, evey number you
enter in A1 will be added to A8.

HOWEVER!: This addition will take place every time you recalculate for
any reason. Even if you enter a number in another cell this will cause
recalculation. And if so, the current number in A1 will be re-added.

HTH
Kostis Vezerides

Signature

vezerid

Reply to this Message