reference to circular reference bug

  • Thread starter Thread starter marco
  • Start date Start date
M

marco

I just discovered something that looks like a new excel bug:
to reproduce behaviour:

1. open a new workbook;
2. activate circular references;
3. enter those formulas:
B4 <- formula is =B5
B5 <- formula is =1+SE(B6;0;1)
B6 <- formala is =(B5=2)
B7 <- formula is =B5 (same as B4!!!)

(NB: SE() this is the standard IIF() function in the italian version
of excel;)

What's wrong: B4 and B7, will display different results even if they
contain the same formula. The result displayed depends on the position
of the cell conataining the formula: if it is on the left or above B5
the result will not be equal to B5.

My explication: The cells before B5 are computed one iteration before
the last, so they are not up to date. I suppose that mr. M$ will say
that this behaviour is by design...

Comments welcome!
Marco
 
The reason is that your circular calculation never converges, it just hits
the maximum number of iterations.

The way circular calculations work is left-to-right, top to bottom.

even if you move the formula in B4 to below it still does not converge
because the cells are changing by more than Maximum Change on each
iteration.

Does not seem fair to blame M$ for failing to solve a non-converging
process: basically there is no "correct" answer to your problem, only
several different ones.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
Back
Top