Circular references? I iterate You what?!

  • Thread starter Thread starter DannyJ
  • Start date Start date
D

DannyJ

first thanks to everyone for their help....I have reinstalled office and I
still cannot use the reply group option.

Next I have this error =(E16+E18)--I16 was defined as a circular reference
but for the life of me I cannot see why. Some of my other formulae are also
"circular"

In English this equation should be (Overtime + overtime carried over from
previous month) minus minus the hours holiday

Why it is cicular I have no idea. It works if I change the itiration. so
my question is in what way is this circular and how does itiration work?

Thanks in advance

Danny
 
Hard to say without seeing what is in E16, E18, I16.

Circular reference is when you can run round your formulas and jump from one
cell to another and then end up back where you started, and this could be a
long run round if you have lots of links. Sometimes used in pricing, when
an overhead that makes up part of the sell price is a percentage of the same
sell price.

Iteration simply allows the formula to run round itself x number of times,
gradually converging on as close to a correct answer as it can, given the
constraints you put on it.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------­------------------------------­----------------
 
Danny

which cell are you putting the formula in ? If it is in E16, E18 or I16 it
is a circular reference.

Regards

Trevor
 
DannyJ said:
first thanks to everyone for their help....I have reinstalled office and I
still cannot use the reply group option.

Next I have this error =(E16+E18)--I16 was defined as a circular reference
but for the life of me I cannot see why. Some of my other formulae are
also
"circular"

In English this equation should be (Overtime + overtime carried over from
previous month) minus minus the hours holiday

Why it is cicular I have no idea. It works if I change the itiration. so
my question is in what way is this circular and how does itiration work?

Thanks in advance

Danny
Click to expand...

Hi Danny,

your formula is "circular" if your target cell (the cell where the formula
is stored) is also an argument in the formula. E.g. if you put your formula
=(E16+E18)--I16 in cell E16.

The circular reference might not be that obvious and might occur across
several cells. The point is that a resulted cannot be calculated if the
result is included in the calculation, thus forming a circular reference.

What kind of iteration is involved here?

Stephan

PS.: The brackets in =(E16+E18)--I16 are not necessary
 
Danny,

You can download a fully functional demo of my XspandXL add-in from my site
below. Hopefully, if your formula is circular, it will show you the exact
calculation chain of the circularity.

Here's an example:

Tested Cell: Main!$M$26

$M$26 with formula: =+M346 is circular, dependent on...
<$M$346 with formula: =+SUM(M343:M345)
<$M$344 with formula: =+M355*(L66+M66)*0.9/2
<$M$66 with formula: =+L66-M179
<$M$179 with formula: =-Z189*M158
<$Z$189 with formula: =1-SUM(Z209:Z214)
<$Z$209 with formula: =M178/-M$158
<$M$158 with formula: =SUM(M154:M157)
<$M$157 with formula: =+M112-L112
<$M$112 with formula: =+L112-M56
<$M$56 with formula: =-M54-M55
<$M$54 with formula: =+M53+M52
<$M$52 with formula: =SUM(M50:M51)
<$M$50 with formula: =SUM(M47:M49)
<$M$47 with formula: =M45+M46
<$M$45 with formula: =+M44+M38
<$M$38 with formula: =M28+M37
<$M$28 with formula: =+M26+M27


Robin Hammond
www.enhanceddatasystems.com
 
Back
Top