Those Circular References...

Ok, Now I am not exactly sure of how to explain this, but here goes...

My boss is trying to create a formula in excel, to calculate interest or
something like that.

He keeps getting a circular reference. So his question is this...

Is there anyway to "Trick" a circular reference. such as for example

cell A1 =Round((B1*33%)/12)

and then in B1 have =G1-G2-G3-G4-G5

He is used to Lotus, and Since I have joined the company in June of last
year, I switched everyone over to Excel, but I'm not very familiar w/
circular references. I try and avoid them as much as possible. So any help
would greatly be appreciated.

Thanks
Jeremy

In the example there is no circular reference, unless any of the G1:G5
formulas relies on A1.
In any case, there is a menu bar called "Formula Auditing", also on the menu
Tools->Formula Auditing, that can help you trace where the circular
references are.

Hope this helps,
Miguel.

"JeremyH1982" wrote:

> Ok, Now I am not exactly sure of how to explain this, but here goes...
>
> My boss is trying to create a formula in excel, to calculate interest or
> something like that.
>
> He keeps getting a circular reference. So his question is this...
>
> Is there anyway to "Trick" a circular reference. such as for example
>
> cell A1 =Round((B1*33%)/12)
>
> and then in B1 have =G1-G2-G3-G4-G5
>
> He is used to Lotus, and Since I have joined the company in June of last
> year, I switched everyone over to Excel, but I'm not very familiar w/
> circular references. I try and avoid them as much as possible. So any help
> would greatly be appreciated.
>
> Thanks
> Jeremy

1. Not that it relates to your question but the round formula needs some
extra info on how to round

=Round((B1*33%)/12,2)

rounds to 2 decimals


2. Neither of these should give a circular reference unless and of the cells
in G1:G5 refers to either B1 or A1 in some way or another


3. To remove the heads up you can go into tools>options>calculation and
select iteration

4. Sometimes circular references can be useful but overall I would recommend
against it because if you have quite a large workbook it can become near to
impossible to audit it for errors if you have circular references



Regards,

Peo Sjoblom


"JeremyH1982" wrote:

> Ok, Now I am not exactly sure of how to explain this, but here goes...
>
> My boss is trying to create a formula in excel, to calculate interest or
> something like that.
>
> He keeps getting a circular reference. So his question is this...
>
> Is there anyway to "Trick" a circular reference. such as for example
>
> cell A1 =Round((B1*33%)/12)
>
> and then in B1 have =G1-G2-G3-G4-G5
>
> He is used to Lotus, and Since I have joined the company in June of last
> year, I switched everyone over to Excel, but I'm not very familiar w/
> circular references. I try and avoid them as much as possible. So any help
> would greatly be appreciated.
>
> Thanks
> Jeremy

to calculate interest without circulare refs:
- calculate cash balance before interest
- calculate interest on cash balance
- calculate cash balance + interest

Charles
_____________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

"JeremyH1982" <[email protected]> wrote in message
news:[email protected]...
> Ok, Now I am not exactly sure of how to explain this, but here goes...
>
> My boss is trying to create a formula in excel, to calculate interest or
> something like that.
>
> He keeps getting a circular reference. So his question is this...
>
> Is there anyway to "Trick" a circular reference. such as for example
>
> cell A1 =Round((B1*33%)/12)
>
> and then in B1 have =G1-G2-G3-G4-G5
>
> He is used to Lotus, and Since I have joined the company in June of last
> year, I switched everyone over to Excel, but I'm not very familiar w/
> circular references. I try and avoid them as much as possible. So any help
> would greatly be appreciated.
>
> Thanks
> Jeremy