clear circular reference fault?

[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

[Bernard Liengme]

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"George" <[email protected]> wrote in message
news:[email protected]...
>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

[George]

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

On Sun, 2 Oct 2005 14:18:53 -0300, "Bernard Liengme"
<[email protected]> wrote:

>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

[Dave Peterson]

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 wrote:
>
> 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
>
> On Sun, 2 Oct 2005 14:18:53 -0300, "Bernard Liengme"
> <[email protected]> wrote:
>
> >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

--

Dave Peterson

[Bryan Hessey]

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.

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

On Sun, 2 Oct 2005 14:18:53 -0300, "Bernard Liengme"
<[email protected]> wrote:

>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

[George]

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?
> [We] don't want to get involved with answering that for you.

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?

No? URL(s)?

G


On Sun, 2 Oct 2005 19:13:38 -0500, Bryan Hessey
<[email protected]> wrote:

>
>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.
>
>George Wrote:
>> 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
>>
>> On Sun, 2 Oct 2005 14:18:53 -0300, "Bernard Liengme"
>> <[email protected]> wrote:
>>
>> >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

[George]

On Sun, 02 Oct 2005 18:13:20 -0500, Dave Peterson
<[email protected]> wrote:

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

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

[Bryan Hessey]

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?
> [We] don't want to get involved with answering that for you.

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?

No? URL(s)?

G


On Sun, 2 Oct 2005 19:13:38 -0500, Bryan Hessey
<[email protected]> wrote:

>
>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.
>
>George Wrote:
>> 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
>>
>> On Sun, 2 Oct 2005 14:18:53 -0300, "Bernard Liengme"
>> <[email protected]> wrote:
>>
>> >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