clear circular reference fault?

  • Thread starter Thread starter George
  • Start date Start date
G

George

I have a worksheet with a number of circular references. I have
iteration turned on, and it seems to do what I want. But, if I make
some particular mistakes (eg: entering really unreasonable data), I
get into a situation where most of the cells are #VALUE. The only way
I've found to clear this is to re-load a good copy of the sheet.
'Undo' has no effect.

Are there better ways to recover from this?

More generally: my knowledge of Excel is kind of spotty. I don't have
the time to become expert in Excel, but sometimes I want to use some
particular more advanced feature. The built-in help seems kind of
limited, unless I missed something in the installation. So, I
flounder around.

Is there an on-line "Excel reference manual", or something?

Thanks,
George
 
You "don't have the time to become expert in Excel" but "want to use some
particular more advanced feature"
I know the feeling!
I don't have time to qualify as a pilot but I want the fly an F18 at Mach 2
 
I'm not sure I understand what you're saying. Could you elaborate?
I'm sure it must have some connection to my question, but I just don't
get it.

G
 
I don't want to speak for Bernard, but I interpret his remarks as saying that
it's not often that users of excel (especially non-experts) use circular
references on purpose.

My bet is that you shouldn't be using them (I could be wrong). I'd spend some
time trying to figure out where they are and how to remove them. (Or verify
that they're really necessary...)

If you have trouble finding them, maybe Stephen Bullen's utility will help:

http://www.oaltd.co.uk/Excel/Default.htm
Look for FindCirc.zip
 
George,

My first suggestion would be that you clear all 'circular' references.

Imagine a circular whereby A1=B1*2 AND B1=A1*2, so you set iterations
to only re-calculate this n~ times, it's still not a wise action.

An experienced user of Excel might be better able to work with circular
references, but as you are having problems then perhaps a worksheet with
standard formula might better serve your purpose.


Also, hi Dave, your post wasn't there when I started.
 
Well, that's a little more clear. I was really confused about the
airplane thing. Now, I think we might paraphrase this to ...
[.. problem description .. ]
Are there better ways to recover from this?
Click to expand...
[We] don't want to get involved with answering that for you.
Click to expand...

Fair enough; responses are voluntary. But, that leaves the 2nd part
of my post, which was put there to cover just such a situation:
Is there an on-line "Excel reference manual", or something?
Click to expand...

No? URL(s)?

G
 
I'd spend some
time trying to figure out where they are and how to remove them. (Or verify
that they're really necessary...)
Click to expand...

I think that I understand the computation I'm doing. IMO, it is
intrinsically iterative: the intermediate result affects a parameter
that affects the result.

Excel appears to converge, to a solution that behaves 'reasonably'.
Field tests are coming, so we shall see.

G
 
I guess another view would be that once you have reached the point where
many cells show #Value and Undo no longer achieves, to being equal to
crashing your F18, how you pick up the pieces doesn't really matter,
you need to avoid reaching that point.

As for the online manual, the full Help is, as you have stated,
built-in. There are many sites that will further assist with 'how-to'
and 'add-ons', but none to my knowledge that will describe why you
would use one function in preference to another. That learning comes
from a tutored (paid) course or from experience.
You could try Joseph Rubin's site, or Pearson, or sites offered by many
other MVP's, or Debra Dalgleish's site
http://contextures.com/tiptech.html


Well, that's a little more clear. I was really confused about the
airplane thing. Now, I think we might paraphrase this to ...
[.. problem description .. ]
Are there better ways to recover from this?
Click to expand...
[We] don't want to get involved with answering that for you.
Click to expand...

Fair enough; responses are voluntary. But, that leaves the 2nd part
of my post, which was put there to cover just such a situation:
Is there an on-line "Excel reference manual", or something?
Click to expand...

No? URL(s)?

G


George,

My first suggestion would be that you clear all 'circular' references.

Imagine a circular whereby A1=B1*2 AND B1=A1*2, so you set iterations
to only re-calculate this n~ times, it's still not a wise action.

An experienced user of Excel might be better able to work with circular
references, but as you are having problems then perhaps a worksheet with
standard formula might better serve your purpose.


Also, hi Dave, your post wasn't there when I started.
Click to expand...
Click to expand...
 
Back
Top