Here is the second part of a tutorial in both elementary dynamics and numerical methods.
It is written at a basic level and it shows you how to solve a system of difference equations in an Excel table.
It also starts to explain how to animate the model.
[sociallocker]
[/sociallocker]
A casual approach to numerical modeling – the Spring-Mass-Damper System – part 2.
by George Lungu
It contains a tutorial about the implementation of a static SMD model in Excel
<excelunusual.com>
Use Excel to make a simple “accounting style” spread sheet with few hundred time steps and adjustable oscillation parameters. Name this workbook “Osc_1”.
<excelunusual.com> 2
Start with label cells :
Cell A1: “M = “ , Cell A2: “K” , Cell A3: “DC”, Cell A4: “Delta t = “ , Cell B7: “t” , Cell C7: “a”, Cell D7: “v “ , Cell E7: “x”
Continue with parameter cells (customizable constants):
Cell B1: “1”, Cell B2: “1”, Cell B3: “0.3”
Cell B4: “1“, Cell B8: “0”, Cell D8: “0”
Cell E8: “-0.3”
Finish up with active formula cells :
Cell B9: “=B8+B$4”
Cell C9: “=-(E8*B$2+D8*B$3*2*SQRT(B$1*B$2))/B$1”
Cell D9: “=D8+C9*B$4“ Cell E9: “=E8+D9*B$4”
<excelunusual.com> 3
Next, copy the range of “B9:E9” all the way down to “B1008:E1008”.
And we are almost ready to simulate after we display the coordinate x (E8:E1008) function of time t (B8:B1008) in a scatter plot.
Note:
There is a small deviation from the initial formulas, namely in cell C9 the damping coefficient was scaled by “2*sqrt(M*K)”.
This way the unit threshold for the damping coefficient indicated the onset of oscillation regardless of the mass or elastic constant of the spring.
<excelunusual.com> 4
Create the chart
Click on an empty cell -> Insert -> Chart -> ->
XY (Scatter) -> Finish
Create the chart
Right click on the chart window -> Source
Data -> Series -> Add
<excelunusual.com> 6
We highlight:
(B8:B1008) for the X Values
(E8:E1008) for the Y Values
(Detail)
ending up with something like this -> then click OK
<excelunusual.com> 7
• Highlight and delete the legend
• Adjust your axes’ scale and fonts to your liking
• Add title and axes captions if you wish
• Format the plot area and the grid lines
• Format data series
• Use the Help menu for any or more of these options
<excelunusual.com> 8
Create buttons by doing the following operations:
View -> Toolbars -> Control Toolbox -> Design Mode
Drag a Spin Button from the palette onto the sheet.
Size the button accordingly, then copy the button 3 times
and place them in cells> “C1”, “C2”, “C3”, “C4”
9
Right click on each button, select “Properties
and change the following features (in red):
Placement Name: Min: Max:
Button # 1 Cell “C1” M 1 50
Button # 2 Cell “C2” K 1 50
Button # 3 Cell “C3” DC 0 100
Button # 4 Cell “C4” delta_t 1 50
After you finish, click “Exit DesignMode” on the Control Toolbox, then close the Control Toolbox.
<excelunusual.com> 10
Create the macros:
Hit Alt-F11 and bring up the macro editor. Selecting the current sheet on the left side, type the following
macros to the right side (the editor space) :
Private Sub M_Change()
Range(“B1”) = M.Value / 10
End Sub
Private Sub K_Change()
Range(“B2”) = K.Value / 10
End Sub
Private Sub DC_Change()
Range(“B3”) = DC.Value / 50
End Sub
Private Sub delta_t_Change()
Range(“B4”) = delta_t.Value / 100
End Sub
<excelunusual.com> 11
A few cases:
Under-damped
Critically-damped
Over-damped
<excelunusual.com> 12
Let’s have a look at worksheet Osc_2:
Animation:
My father is a physics teacher and I heard him lecturing many times since I was a little child. Though I had an inner
admiration for his craft I found it boring. Occasionally though I had the chance to visit the school’s physics lab where all the
sterile theory came to life.
To get both, maximum motivation and impact on audience, it is nice to mix science with show. Ideally we need the following:
– interesting real life examples
– animation and real-time simulation
– on-the-fly parameter controls
– colors and possibly sounds
<excelunusual.com> 13
How do we animate our simulation?
For now, let’s just have a circle floating in space on an XY scatter plot. The “y” coordinate will be zero but the x coordinate of the circle
will correspond to the “x” coordinate of our simulation at different moments in time.
time [s]
1 2 3 4 5 6 7 8
Start by copying the workbook into a new workbook called Osc_2
<excelunusual.com> 14
Let’s add the following information:
Label cells :
Cell A5: “Increment“
Cell G24: “X_mass”
Cell H24: “Y_mass”
Parameter cells (customizable constants):
Cell B5: “0“ – integer representing the time step, this cell will contain the simulation “increment” which is can go from 0 to 1000 and it is driven by a macro.
Cell H25: “0” – represents the “y” coordinate of the mass
Active formula cells:
Cell G25: “=OFFSET(E8,B5,0)“
This cell will display the “x” coordinate of the mass M at the time step displayed in cell B5.
<excelunusual.com> 15
Create an XY scatter plot having the following source data:
x = Range(“G25”), y = Range(“H25”)
Also go to: View -> Tool Bars -> Draw
Create a green textbox with the word “Start” inside
New text box
New Chart
<excelunusual.com> 16
The new chart and its source data
<excelunusual.com> 17
We need to write the following macro which would run cell “B5” as a counter from 0 to 1000 allowing the x coordinate of the mass to change:
Sub Start()
For i = 0 To 1000
DoEvents
Range(“B5”) = i
Next i
End Sub
Right click the “Start” button and assign the above macro to it.
<excelunusual.com> 18
Outline of the static old static model:
Until now we have had a static simulation since all the model calculations are in a fixed table:
Initial conditions
Formulas
In this model the number of time steps is equal to the number of rows in the table of formulas
<excelunusual.com> 19
Advantages of the static model:
1. Programming ease (minimal VBA code)
2. Speed. The computation is parallel and Excel is optimized for this.
The speed of running numerical modeling in Excel is very good exceeding Simulink for
instance (exception is Excel 2007 which is a dog and should be avoided if possible).
Drawbacks of the static model:
1. Large files (the number of redundant formulas is proportional to the number of time steps)
2. Short runs. The number of time steps is limited to about 65000 in Excel 2003 or earlier
by George Lungu <excelunusual.com>
