This is a tutorial about the creation of an animated diffusion model based on the random walk principle.

There are two different models, one based on a lattice style diffusion (the particles can move in a series of steps of a fixed distance, but only in multiples of a 90 degree angles: 0, 90, 180, 270) and another one based on a fluid style diffusion in which the particles can move along any angle yet they can move a fixed distance for any step.

## A 2D Random Walk Diffusion Animation Tutorial

by George Lungu

– We have seen it the last tutorial how to model and animate

the trajectory of a particle moving under random walk rules.

Two models were developed. In the first one the random walk

was confined to a square lattice (the angle of movement was

random but could take only four values (0, 90, 180 or 270)

with respect to the x axis.

In the second one (suitable for

modeling diffusion in fluids) the angle of movement was

continuously and uniformly distributed between 0 and 360

degrees with respect to the x axis (free random walk).

-This presentation introduces the reader to

a couple of animated diffusion models using

a random walk approach. Each of the

models have 10,000 particles and one uses a

lattice type rule of movement and the other

uses a free rule of random walk movement.

<excelunusual.com> 1

### The random walk diffusion model overview:

– We will model 10,000 particles which perform a random walk movement. A Reset macro will

bring all the particles in origin by setting their coordinates to zero and a Start_Pause will

emulate the time flow by copying the 10000 x-y current coordinates and pasting them one

time step in the past during each of a conditional “Do” loop cycle within the Start_Pause macro.

– Besides the 10,000 row tall table with random numbers which determine each particle jump

direction and 10,000 tall tables with the particle current and past coordinates our worksheet

must contain a small table (50 row tall) which at any moment calculates the particle density

function of the radius around the start point (origin).

– Both the 10,000 animated particles as well as their radial density function will be displayed

“real-time” on two different charts.

### The Excel implementation:

– Let’s create two worksheets one based on a digital random walk (lattice confined) and one

with an analog angle version. Name the first worksheet “Diffusion_Lattice”

– The following ranges contain labels: A22, A27, N27, A29:K29

-This will be a dynamic model run as a “Do” loop. Cell B27 (Index) keeps track of the number of

times the loop was run since the last reset.

-This will be a dynamic model run by a “Do”

loop. Cell B22 (Index) keeps track of the number

of times the loop was run since the last reset.

Cell O27 contains an adjustable increment for

calculating the radial density function.

<excelunusual.com> 2

### The coordinate calculation area for the particle group:

– Range A31:A10030 contains integer random numbers uniformly distributed between {0, 1,

2, 3} : A31: “=INT(4*RAND())” then copy A31 down to A10030.

– Range B31:C10030 contains the x-y coordinates of the 10,000 particles just one time step

before the current moment.

This range is being cleared by the “Reset” macro and it is being

refreshed with past coordinates by the “Start_Pause” macro: Nothing needs to be entered here.

– Range D31:D10030 contains the x coordinates of the 10,000 particles during the current

time step.

The current x is calculated recursively from the previous time step x adding a

random jump (the particle jumps to the right if the reference random number is equal to “0”

and to the left if the reference random number is equal to “2”: D31: “=B31+IF(A31=0,$B$27,0)-

IF(A31=2,$B$27,0)” then copy D31 down to D10030.

– Range E31:E10030 contains the y coordinates of the 10,000 particles during the current

time step.

The current y is calculated recursively from the previous time step y adding a

random jump (the particle jumps forward if the reference random number is equal to “1” and

backwards if the reference random number is equal to “3”: E31: =C31+IF(A31=1,$B$27,0)-

IF(A31=3,$B$27,0)” then copy E31 down to E10030.

The radial density distribution calculation area:

– Range G31:G10030 contains the current distance between each particle and the origin

obtained using Pythagoras theorem: G31: “=SQRT(D31^2+E31^2)” then copy G31 down to G10030.

– The density function will be plotted for a series of increasing radii which will be contained in

range I31:I81: I31: “=0” , I32: “=I31+O$27” then copy I32 down to I81.

<excelunusual.com> 3

– Range J31:J80 contains the current number of particles contained within a circle of a certain radius centered in origin:

J31: “=COUNTIF($G$31:$G$10030,”<=”&I32)

then copy J31 down to J80.

– The density function is calculated in the range K31:K80 : K31: “=J31/(2*PI()*I32^2)” ,

K32: “=(J32-J31)/(2*PI()*(I33^2-I32^2))” then copy down to K81.

The spin buttons and the associated macros:

Private Sub Radius_Increment_Change()

– All the macros are run by buttons with the same name with the macro and having Min=1 and Max=100.

[O27] = Radius_Increment.Value

End Sub

Private Sub Step_Size_Change()

[B27] = Step_Size.Value / 10

End Sub

– The third macro adjusts the scale of the particle animation chart. In order to make that macro work we need to rename the chart “ChartA”.

Private Sub Scale_XY_Change()

ActiveSheet.ChartObjects(“ChartA”).Chart.Axes(xlCategory).MaximumScale = Scale_XY * 100

ActiveSheet.ChartObjects(“ChartA”).Chart.Axes(xlCategory).MinimumScale = -Scale_XY * 100

ActiveSheet.ChartObjects(“ChartA”).Chart.Axes(xlValue).MaximumScale = Scale_XY * 100

ActiveSheet.ChartObjects(“ChartA”).Chart.Axes(xlValue).MinimumScale = -Scale_XY * 100

End Sub

<excelunusual.com> 4

### Creating the particle animation chart and the radial density chart:

– Create a 2D scatter chart having B31:B10030 as the x-

range and C31:C10030 as the y-range, this will display the

10,000 particle animation.

– Create a second 2D scatter chart having I31:I80 as the x-

range and K31:K80 as the y-range, this will be the animated

radial density chart.

### Rename the particle animation chart:

– Make the “Draw” toolbar visible. Click on the “Select Objects” arrow icon on the bottom of the

page, then select the particle chart. The chart is now highlighted with small white circles in the

corners. On right top of the page in the “Name Box” rename the chart “ChartA”, then hit return

Public s As Boolean

### The dynamic (animation) macros:

Sub Start_Pause()

s = Not (s)

Do Until s = False

macro is needed to crate animation.

DoEvents

– The “Reset” macro will delete all historical data.

– “s” is a Boolean variable and can take only two values: true of false. The purpose of this variable is to keep track if the active macro (Start_Pause) runs or it is stopped. Another purpose of this variable is to stop the active macro if the macro is triggered while the conditional “Do” loop is running.

[B22] = [B22] + 1

[B31:C10030] = [D31:E10030].Value

DoEvents

Loop

End Sub

———————————————————

Sub Reset()

[B22] = 0

[B31:C10030] = 0

End Sub

<excelunusual.com> 5

-The “Start_Pause” macro contains a conditional loop. If the loop is not running it means “s = False”.

Clicking the “Start / Pause” button will flip the “s” variable from False to True and the loop will start

and continue to run until the button is clicked again. We can see therefore that “s” has the main role of

being able to both start and stop the macro using the same button.

– The conditional “Do” loop within the “Start_Pause” macro copies all the run data and pastes it one

time step in the past (tow columns to the left), therefore, effectively simulating the passage of time and

dynamically advancing the calculations in time.

### Create a new worksheet with a free random walk based diffusion model:

– Copy the first worksheet “Diffusion_Lattice” and rename it “Diffusion_Free”. This will be a model similar to the previous, except that

the particle is not confined to a lattice for every step but it can move in any direction by a random angle uniformly distributed between 0 ad 2.

previous

– Leave everything the same except for changing the active formulas in the ranges A31:A10030, D31:D10030, E31:E10030.

Cell A31: “=2*PI()*RAND()” this formula will generate a new random number between 0 and 2 each time the worksheet is recalculated

Cell D31: “=B31+B$27*COS(A31)”

Cell E31: “=C31+B$27*SIN(A31)” – the last two formulas will

move the particle with respect to the previous position by a value equal to

one spatial step size and in a random direction based on a uniformly

distributed angle (0 to 2 with the horizontal).

After you insert the three

formulas fill down the full ranges down to row 100030.

by George Lungu <excelunusual.com> 6

Thanks for the correction JS. Unfortunately my originals burned in the great Colorado fire in June with all he house and I don’t have the time to redo the whole presentation. All the best! George

Hi nice model – hours of fun, Reading your tutorial with expressions for the density function – have you not slipped an extra factor of 2 into the spreadsheet densitry calculation that ought not to be there? Apologies if I’ve not followed the plot too well.