Cyclic references and random numbers

[J.Ty.]

Hi Everyone,


I have discovered a strange behavior of the random number generator and cyclic references.


Please have a look at the following simple spreadsheet (tested in Excel 2016 64 bit):

A1 is

=OFFSET(B1,0,RANDBETWEEN(-1,0))

B1 is

=NOW()

A quick analysis shows that whenever RANDBETWEEN(-1,0) draws -1 a cyclic reference emerges and Excel should report it. I normal circumstances one should expect the window with the warning to show up roughly every second F9. However, it is much less frequent.


In order to make sure that it is really due to the behavior of the formula, not the reporting system, I have set a conditional formatting rule which makes cell A1 red whenever its value differs from B1. Now, after each recomputation the value of B1 changes; A1 changes if and only if
RANDBETWEEN(-1,0) draws 0, because otherwis eit becomes a part of a cycle and points to itself with the old value.
The effect persists: the cycle warning and/or red color are much less frequent than 50%.


Then I set the itrative calculations on. It switches off the warning window, only the red color indicates the cycle. Still, it is less frequent than 50%.


Can anybody explain this?


J.Ty.

 

[XOR LX]

Hi.

I'm interested in how you recorded results such that you were able to then conclude that the occurrence of a circular reference in this set-up is "less frequent than 50%".

Regards

 

[J.Ty.]

I have been pressing F9 and counting how often I get a window with the warning.

You can make a larger experiment if you wish, like below, with 1000 rows. Each row is a separate experiment of the same kind as in the first post. The iterative calculations are enabled, with 1 iteration. It means that there is no real cyclicng, but the formula is allowed to read the old value of the same cell. Again, drawing -1 means that you get the old value in the cell, which should therefore be the timestamp from the previous evaluation. If you draw 0, you fetch the new timestamp.

The array formula then reports how many times the value in column A is smaller than the value in column B (indicating that -1 has been drawn). Typical values are 0,1,2. I have seen 3 and 4. But the expected number is 500.


J.Ty.

Excel 2016 64 bit

	A	B	C

[TD="align: center"]1[/TD]
[TD="align: right"]42508.53941[/TD]
[TD="align: right"]42508.53941[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]42508.53941[/TD]
[TD="align: right"]42508.53941[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]42508.53941[/TD]
[TD="align: right"]42508.53941[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]42508.53941[/TD]
[TD="align: right"]42508.53941[/TD]
[TD="align: right"][/TD]

Sheet2​

[TABLE="width: 85%"]
[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH]Formula[/TH]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A1[/TH]
[TD]=OFFSET(B1,0,RANDBETWEEN(-1,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD]=NOW()[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A2[/TH]
[TD]=OFFSET(B2,0,RANDBETWEEN(-1,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD]=NOW()[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[/TABLE]

[TABLE="width: 85%"]
[TR]
[TD]Array Formulas[TABLE="width: 100%"]
[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH]Formula[/TH]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C1[/TH]
[TD]{=SUM(--(A1:A1000<B1:B1000))}[/TD]
[/TR]
[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

[/TD]
[/TR]
[/TABLE]

 

[XOR LX]

I suspect your choice of NOW() is complicating matters with respect to the order of calculation here.

Perhaps the iterations are stopped prematurely since, no matter how small a value you choose for the Maximum Change for iterations, Excel considers all values in the circular reference you have constructed to have changed by an amount less than this value, and hence stops calculating, as it is set to do.

For example, if you instead try this small adjustment to your set-up:

Using a newly-created Excel Table (this seems to help control the initial calculation) with headers in row 1 and last row 1001, enter your formula, i.e.:

=OFFSET(B2,0,RANDBETWEEN(-1,0))

in A2.

In C2, enter:

=RANDBETWEEN(1,3)

Then, in B2, enter some random initial character, e.g. "x", which you will then need to manually copy down to row 1001.

Now, amend the entry in B2 to the formula:

=B2+C2

You should get a box asking you to whether to "Overwrite all cells in this column with this formula", which you should then do.

Your counting formula, i.e.:

=SUMPRODUCT(--(A2:A1001<B2:B1001))

should now return a result close to 500.

Repeated pressing of F9 will now produce counts following a geometric progression in the region of 750, 875, 937.5, etc., as would be expected.

Regards

 

[J.Ty.]

I suspect your choice of NOW() is complicating matters with respect to the order of calculation here.

Perhaps the iterations are stopped prematurely since, no matter how small a value you choose for the Maximum Change for iterations, Excel considers all values in the circular reference you have constructed to have changed by an amount less than this value, and hence stops calculating, as it is set to do.

Thanks for you input, but "stopping prematurely" is unlikely to be an explanation, because I set the number of iterations to be 1.

Generally, I know how to modify my experiment to get something close to the expected 500.

I want to understand why I do not get anything close to 500 in my original experiment, because it contradicts my present understanding how Excel treats NOW() and how it executes circular computations.

J.Ty.

 

[shg]

Curious.

I get a similar result with

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]

0.97939​

[/td][td]A1: =IF(RAND()>0.5, A1, A2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

0.97939​

[/td][td]A2: =RAND()[/td][/tr]
[/table]

 

[J.Ty.]

Thanks, shg! So it is possible to get this effect without NOW().

J.Ty

 

[XOR LX]

Though perhaps the fact that you have both chosen volatile functions as part of your set-ups is of importance:

"Volatile cells that are not dependent on the cells in the circular list are NOT recalculated on each iteration."

(From Excel Calculation Process - Decision Models)

Regards

 

[J.Ty.]

XOR LX,
I have set Excel to 1 iteration, so there is never a second iteration. In shg's experiment Excel does not even do circular computation, merely discovers the cycle and displays the warning. And the frequency of this event is much lower than one would expect.

J.Ty.