Those Circular References...

[JeremyH1982]

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

 

[Miguel Zapico]

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.

 

[Peo Sjoblom]

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

 

[Casey]

Jeremy,
In the two formulas you posted there is no circular reference, however
the formula in A1 is incomplete as there are too few arguments for th
round function. The formula should be something like:

=ROUND((B1*33%)/12,1) with the ,1 indicating the number of decima
places you would like to round to.

For you to have a circular reference the second formula would have t
refer back to either Cell A1 or B1.

HT

 

[Charles Williams]

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