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

Those Circular References...
 

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

Those Circular References...
 

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 toolsoptionscalculation 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

Those Circular References...
 

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 the
round function. The formula should be something like:

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

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

HTH


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=544415

Those Circular References...
 

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" wrote in message
...
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