# Sand Pendulums – Lissajous Patterns in Excel

Few days ago, I saw a beautiful homemade science experiment on Sand Pendulums on Bruce Yeany’s YouTube channel. Go ahead and check it out. It is a cool project to do with your kids.

I will try this experiment with kids during school term holidays around Easter. But first, I wanted to try the simulation in Excel.

### Simulating sand pendulum pattern in Excel

Take a look at the final simulation.

**Pendulum pattern – Lissajous curve**

The patterns generated by sand pendulum are nothing but **Lissajous curves**. We can generate these curves in Excel by using below equations.

- X = A * SIN(a*t + d)
- Y = B * SIN(b*t)

where

- A & B refer to amplitude (the length pendulum travels before reversing its direction) along X & Y axes
- a & b refer to height of pendulum along X & Y axes (if these are same you get a simple pendulum and thus a straight line pattern)
- d refers to degrees of shift (its a bit complex to explain here, but read about phase shift on Lissajous curve Wikipedia page)
- t refers to radians (degrees).

**But this will generate a smooth line, how to show sand?**

Simple, after computing each pair of (X, Y) **we add a small random noise** to them. This creates an impression of sand falling from pendulum on to a surface and bouncing off. Let’s define our new equations as,

- X = A * SIN(a*t + d) + jx * rand()
- Y = B * SIN(b*t) + jy *rand()

Where jx & jy are jitter fractions (very small numbers, lest the dots will be too away from original points)

**What if the pendulum never stops?**

Since we are simulating the movement of a pendulum in Excel, we can choose to have a never stopping pendulum (ie a simple gravity pendulum). In this case A & B values never change.

In real life, A&B will reduce with each oscillation until the pendulum comes to a stop (because there is air drag, friction and other forces at play too).

### Let’s look at the chart & VBA

Enough physics & maths. Let’s take a look at the chart & VBA behind this simulation.

- Set up 3 columns, one with t values starting from 0 and increasing by 0.05 per cell, next two with X & Y values.
- Leave the X&Y values blank. We will use VBA to fill these.
- Let’s say we use 5000 (x,y) values to plot the chart. This gives enough variation. You can alter the number of rows later.
- Create a scatter plot with smoothed line using these 5000 (x,y) values.
- Using VBA, iterate thru column t and calculate X & Y values for each t value using above equations.
- Update the chart by using doEvents after every
*n*calculations. Try n=25 first and change as you see fit. Updating the chart after every calculation slows down the animation significantly. - Change A, B, a, b, d, jx, jy and air drag factors to generate different curves.

Here is the VBA code used for the animation.

```
Sub animate()
Dim xVal As Double, yVal As Double, jx As Single, jy As Single
Dim t As Range
Dim a1 As Single, drag As Single
Dim a2 As Double, b2 As Double, d As Double
Dim updateScreen As Integer
Dim tVals As Range
a1 = 1
drag = Range("air.drag")
a2 = [a.2]
b2 = [b.2]
jx = [j.x]
jy = [j.y]
d = WorksheetFunction.pi() / Range("d")
Set tVals = Range("t.vals")
Range(tVals.Offset(, 1), tVals.Offset(, 2)).ClearContents
Range("done") = "drawing..."
For Each t In tVals
xVal = a1 * Sin(t * a2 + d) + jx * Rnd()
yVal = a1 * Sin(t * b2) + jy * Rnd()
t.Offset(, 1).Value = xVal
t.Offset(, 2).Value = yVal
'update screen after every 25 times this loop has run
updateScreen = IIf(updateScreen = 25, 0, updateScreen + 1)
If updateScreen = 0 Then DoEvents
'Reduce A & B values by using drag
a1 = a1 * (1 - drag)
Next t
Range("done") = "done"
End Sub
```

### Download Pendulum Sand Patterns Workbook

**Click here to download the Pendulum Sand patterns workbook**. Play with the animate & random pattern buttons to see some cool patterns.

### Have you done this experiment?

This is a cool way to teach kids the awesome pattern power of simple things in life. Have you done this experiment? If not, give it a try. If you think playing with sand is too messy, try the Excel workbook.

Also check out: 3D dancing pendulums post to see some cool & clever animations. Huge collection of spreadsheet tools & simulations for teachers.

### Introducing our Online Power BI Class:

*Would you like to join me on a date with Power BI?* In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.

**Click here to know more and join us.**

### Leave a Reply

Reshaping your data easily – Case study [Pivot tables FTW] |
Hide columns one one tab same way as they were in another place [quick tip] |