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).
– First let’s rename the second worksheet (Tutorial_2) as “Tutorial_2&3
– In cell Q31 type the following formula:
– 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
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).
– The picture to the right shows an example
of a histogram in the ordinary form and the
– 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
– 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
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
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)
– 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
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
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
this purpose. If n > 200 Then
n = -200
– The macro uses a conditional “While” loop to increment an
integer variable which will be assigned to the offset cell. This way
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.
– Depending on the offset value these halves might slightly overlap
<excelunusual.com> End Sub
Example of interesting looking correlation charts:
to be continued…
6 Replies to “An Animated Linear Feedback Shift Register (LFSR) as a Pseudo Random Pattern Generator in Excel 2003 – Part#3”
Thanks for sharing all this amizing stuff with us. I would like to ask you how can i stimulate 2 or 3 dimensional heat transfer using excel? Thanks
Hi Theo, They are both easy to simulate and if you go back you can find a series of tutorials (search the word heat). The problem with 3D (which is done just like 2D is the visualization. Excel is not powerful enough for this type of visualization. Simulating 3D heat transfer is no problem though. George
I wold like to learn or known how to do logarithmic or semi logarithmic graph in excel?
Log charts are standard options in Excel. Just click and format the axes and in the menu there is a log/lin option.
Thanks once again for this amazing sharing of ideas.
How wold like to learn or known how to do logarithmic or semi logarithmic graph in excel?
Comments are closed.