An Animated Linear Feedback Shift Register (LFSR) as a Pseudo Random Pattern Generator in Excel 2003 – Part#3

This is the third part of a tutorial (1, 2, self) describing the creation of an animated Pseudo Random Number Generator model as a Fibonacci type Linear Feedback Shift Register in MS Excel 2003. This section charts the pseudo random series after converting it from binary 14-bit numbers into decimal numbers scaled within the interval [0,1). A correlation chart is then created, displaying the first half of the series function of the second half, with a small adjustable offset (between -200, and 200). Two simple macros are used, one for the manual offset adjustment and one for the animated offset adjustment. Changing the offset (either manually or using the animated macro) one can notice various not-so-random patterns for certain offset values.

An Animated Linear Feedback Shift Register (LFSR) as a Pseudo Random Pattern Generator in Excel 2003 – Part#3

by George Lungu

– In the previous section, a table type implementation of a LFSR was created

– This section converts the 14-bit binary pseudo-random number series into a fractional
series of numbers between 0 and 1 then it display this series of numbers against an offset
version or itself on a 2D scatter chart using a variable offset.

Converting the binary 14-bit numbers into fractional numbers in the range [0,1]:

– We will use the following formula:

– In the above formula “n” represents the number in decimal-fractional form in the range
[0,1) and a0, a1, a2, …, a13 represent the bits of the original binary number with a0 being the
least significant bit (LSB) and a13 being the most significant bit (MSB).
Worksheet implementation:

– First let’s rename the second worksheet (Tutorial_2) as “Tutorial_2&3

– In cell Q31 type the following formula:
Q31: “=B31/2+C31/2^2+D31/2^3+E31/2^4+F31/2^5+G31/2^6+H31/2^7+I31/2^8+J31/2^9+K31/2^10+
L31/2^11+M31/2^12+N31/2^13+O31/2^14”

– Drag-copy cell Q31 down to cell Q16414

Charting the pseudo random series:

– Select range Q31:Q16141 then: Insert => Chart => Line => Finish

– Select the series on the chart by double clicking it, then reduce the marker size to “3”, change the
color of the marker to your preference, change the shape to solid round and choose “Line => None”.
You could change the color of the plot area if you wish by double clicking it.

– The numbers composing the series appear to be uniformly distributed between 0 and 1
1.2

The Pseoud Random Series Chart

1 764 1527 2290 3053 3816 4579 5342 6105 6868 7631 8394 9157 9920 10683 11446 12209 12972 13735 14498 15261 16024

Creating a histogram of the pseudo random series:

– In statistics, a histogram is a graphical representation showing a visual impression of the distribution of
data. It is an estimate of the probability distribution of a continuous variable and was first introduced by
Karl Pearson (from Wikipedia).
<excelunusual.com>
2

– The picture to the right shows an example
of a histogram in the ordinary form and the
cumulative form

– You can see that the x axis of the ordinary
histogram represents the outcome of a certain
random variable and on the y axis there is the
frequency (number of occurrences) of events of
the same variable

Implementing a 71-bar histogram of our pseudo random series in the worksheet:

– We will create a 71-bar histogram using worksheet formulas

– Z31: “=-0.2”, Z32: “=Z31+0.02” then drag-copy Z32 down to cell Z101, AA31: “=0”

– AA32: “=COUNTIF(R$31:R$16413,”<“&Z33)-COUNTIF(R$31:R$16413,”<“&Z32)”

– Drag-copy AA32 down to cell
AA100, then AA101: “=0”

– Chart range Z31:AA101 as a 2D
scatter chart

– We can observe the uniform
distribution of the variable within
the range [0,1).

-We can also verify the uniformity
of the distribution just by visual
inspection of the histogram table
<excelunusual.com>
3

An interesting idea – the correlation chart:

– The 14-bit LFSR device modeled in this presentation is based on the
maximum length linear polynomial and it is supposed to generate 214-1 =
16383 different output binary combinations.

– If we split the series of outcomes in half and plot the first half of the
series on the x axis and the second half of the series of the y axis of a
scatter chart we expect to see a relatively uniform distribution of points
across the charting area

– This uniform distribution is expected because the 16383 numbers are
different from each other and random

– If the numbers are not perfectly random (the first half is somewhat
correlated to the second half) we should see some interesting patterns
such as for instance if the series were equal we would see a 45 degree line crossing the chart.

– It would be interesting to insert a small programmable offset between the two groups of data and
observe the changes in the chart and any relatively ordered patterns that might arise at certain offset
values.

Implementing the correlation chart:

– Cell S31: “=OFFSET(R31,$V$28+2^13,0)”

– Drag-copy S31 down to cell S8223

– Column S will now contain the second half of the series in
column R provided the value in cell V28 is zero

– We can later manipulate the value of cell V28 so that it
provides adjustable offset of the data in column S from the
second half of the original series (column R)
<excelunusual.com>
4

– The scatter chart used to display the correlation plot
will have the range set from 0 to 1 for both x and y axes
and the charted data will be contained in the range
P31:S8223

The manual offset macro:

– The following macro, attached to a spin button will
control the manual adjustment of the offset in cell V28

– The spin button is set to run between -200 and 200

Private Sub Off_Set_Change()
[V28] = Off_Set.Value
End Sub

An animated offset macro: Dim abc As Boolean

Dim n As Integer
– The macro to the right, attached to a button will control the
animated offset routine Sub Animated_Offset_Change()
abc = Not (abc)
– A characteristic of this macro is that it can be started and
Do While abc = True
stopped using the same button. The Boolean variable “abc” serves
DoEvents
this purpose. If n > 200 Then
n = -200
– The macro uses a conditional “While” loop to increment an
Else
integer variable which will be assigned to the offset cell. This way
DoEvents
the user can follow on the chart the effect of plotting a half of the n = n + 1
[V28] = n
random series function on the other half.
End If
– Depending on the offset value these halves might slightly overlap
Loop
<excelunusual.com> End Sub
5

Example of interesting looking correlation charts:
to be continued…
<excelunusual.com>
6