International Journal of Transdisciplinary Knowlegde

ISSN : 2797-4251 (Online)

Journal Homepage : https://ijtk.iainkendari.ac.id/IJTK

Physics Simulation with Excel Spreadsheet

La Ode Asmin1
Institut Agama Islam Negeri (IAIN) Kendari (1)

DOI: 10.31332/ijtk.v1i2.11

 Corresponding author:
[[email protected]]

Article Info Abstract

Keywords: this study present several examples of using Excel spreadsheets to study and
Spreadsheets; simulate physical phenomena, namely projectile motion, falling motion in The
Projectile motion; air, The wave function of particles in a one-dimensional box, and nuclear
decay; radioactive decay. Spreadsheets are used to analyze the motion of projectiles
wave function; with and without resistance, falling objects with different terminal velocities,
Terminal speed. graphs of wave functions and probabilities with different values of n, i.e. n=1 to
n=4, and nuclear decay with different probability values of P. The use of excel
spreadsheets has proven to be very useful in the physics learning process,
namely the rapid response to changes in data and graphs, according to the
values given.

1. INTRODUCTION
In learning physics, media availability is essential to explain natural phenomena
related to physics. Visualization aids or computer simulations are learning media that can
describe physical phenomena. In recent years, the teaching of Physics has changed, namely
by using the internet and simulations. The internet has been used as the primary tool that can
make students actively involved in the learning process in the classroom. Teaching physics
can also be made more interesting and active can also be maintained with simulations.
However, the main thing is the development of methods that can simplify and improve a
learner's ability to understand and develop conceptual understanding. Simulations can be
used to teach concepts in Physics (Toback, Mershin, & Novikova, 2004). Simulating physics
concepts is an interactive and convenient method to gain deep insight into the problem and
its results.
The simplest and widely used method for simulating different physics problems is
based on a spreadsheet program. Spreadsheets have been widely used as tools for teaching
and learning physics. There are two main aspects of using spreadsheets, namely
computational power to solve physics problems of varying complexity and graphical tools to
display the results of these calculations(González, 2018). Simulation is very much needed in
teaching science subjects, especially physics. The use of simulation in teaching physics can
partially replace the role and needs of the laboratory. In general, physics experiments can be
easily carried out in simulations by developing a spreadsheet-based simulator contained in
MS-Excel. Spreadsheet simulation can improve students’ conceptual understanding(Katoch,
2020).

29
International Journal of Transdisciplinary Knowlegde

The potential of excel spreadsheets in learning Physics is the ability to visualize,

simulate, and animate phenomena in Physics with dynamic display of numbers and graphs.
Simulation using spreadsheet media is very effective in helping students learn because the
simulation displays a graphic painting that is formed and is the same as experimenting in a
laboratory. The use of Excel spreadsheet media is very important in abstract physics
learning. Using an Excel spreadsheet will produce simulations similar to actual experiments
in the laboratory. This shows that Interactive learning media help abstract physic concepts
because students can analyze physics phenomena. (Tombade, 2011).
Many reports in the literature have been devoted to the use of spreadsheets to solve
and understand various problems in physics (Baker & Sugden, 2007; Oliveira & Napoles,
2010). Excel spreadsheets are simple and versatile free and computing tools. Excel
spreadsheets are easy to learn and easy to get. Spreadsheets can be used in several fields
such as education, business, and research. It is relatively easy to learn Excel than any other
computer language. Apart from that, spreadsheets are also a practical tool for performing
calculations in both Basic Physics and mechanics, Quantum Physics, and Electronics.
In many works of literature, spreadsheets are widely used to teach physics, including
simulating the time evolution of the chain and plotting the radioactive populations of all
species involved as a function of time. An Excel spreadsheet was used to illustrate Lissajous
charts, Weibull distributions for wind speed, and damped harmonic oscillators(Uddin,
Ahsanuddin, & Khan, 2017). Excel spreadsheets can also be used in teaching basic
statistics(Warner & Judge, 2001).In addition, the Spreadsheet is also used to simulate the
longitudinal vibration of the coupled oscillator using the fourth-order Runge-Kutta method and
to study vertical motion under the influence of a gravitational field(Singh, Kaur, & Khun, 2019;
Grigore & Barna, 2015).In other literature, spreadsheets can be used to teach
electromagnetism by simulation(Squire, Barnett, Grant, & Higginbotham, 2004).
Physics is considered a complex subject. In physics learning, students tend to
understand the material well through giving examples, videos, visualizations, and
simulations. An excel spreadsheet can perform calculations and graphical representations
and simulations. In this article, simulations of several physical phenomena are carried out,
namely projectile motion, falling objects in the air, particle-wave functions, and the radioactive
decay of nuclei.

2. METHODS
Projectile Motion

The input values for the initial parameters are acceleration due to gravity (𝑔), time
interval (𝑑𝑡), initial velocity (𝑣0 ), and constant 𝑏 are 9.8 m/s2, 0.025 s, 20 m/s, 450, and 0.05
m/s2, respectively in cells B5:D5. To facilitate the process of writing formulas for calculations,
rename the "Name Box" of each input parameter value successively with 'g'; 'dt'; 'v_initial';
'start_angle' and 'b'. Then in cells F4:J4 input the amount of time, x-direction velocity, y-
direction velocity, x-position, and y-position. In cell F5, set the time at a value of zero (0) to
cell F119 and cells L5 to L119 with an interval of 0.025. In cells G5 to cell G119 input the
following formula:
=v_initial*COS(RADIANS(Initial_angle)).
In cell H5, input the following formula as v_1y value:
=v_start*SIN(RADIANS(Initial_angle)).
In cell H6, input the following formula: =H5-g*dt and the iteration is v_ny=v_(n-1)y-gdt.
In cell I5, setting the value to 0 (zero), and in cell I6, input the following formula: x2 = x1 +
v2x dt or in excel form:
I6=I5+G6*dt
For the next cell, cells I7 to I119,
x3 = x2 + v3x dt
and the iteration is
xn = xn−1 + vnx dt.
In cell J5, setting the initial position value to 0 (zero), for the next cell input the following
formula: y2 = y0 + v2y dt and in excel form: J6=J5+H6*dt.
For the next cell, cells J7 to J119,
y3 = y2 + v3y dt
⋮
yn = yn−1 + vny dt
Next, in cells J7 to J119, replace the formula with
J7=IF(J6>0;J6+H7*sec;NA())
Furthermore, the formula used for projectile motion with air resistance is as in table 1

Table 1. Description of excel formula for projectile motion simulation

Cells Formulas in Excel
L5 – Input time value starts from 0 to 2.85 s with an interval of 0.025 s
L119
M5 =v_initial*COS(RADIANS(initial_angle))
N5 =v_initial*SIN(RADIANS(initial_angle))
O5 =SQRT(M5^2+N5^2)
P5 Settings = 0 (zero)
Q5 Settings = 0 (zero)
M6 =M5-b*M5*O5*dt
N6 =N5-(g+b*N5*O5)*dt
O6 =SQRT(M6^2+N6^2)
P6 =P5+M6*dt
Q6 =Q5+N6*dt
M7 =M6-b*M6*O6*dt
N7 =N6-(g+b*N6*O6)*dt
O7 =SQRT(M7^2+N7^2)
P7 =P6+M7*dt
Q7 =IF(Q6>0;Q6+N7*dt;NA())

The next step is to create a graph with a spreadsheet to show the relationship between the
horizontal position function and the vertical position function.
Falling Objects in the Air
The first step is to determine and input the quantities used in the calculations. In cells A4 to
D9, input physical quantities, namely gravity (𝑔), number of steps (𝑛), time interval (𝑑𝑡),
terminal velocity (𝑣𝑡𝑒𝑟𝑚𝑖𝑛𝑎𝑙 ), mass (𝑚), and coefficient of drag (𝑏). For the value of 𝑏 input the
formula
𝑚𝑔
𝑏 = 𝑣2 in excel with =m*g/vterm^2
terminal

31
International Journal of Transdisciplinary Knowlegde

Figure1. Input Parameter Display

In cells A13 to D13, enter the quantities of time, position, velocity, and acceleration. After
that, cell A14 as the starting time, B14 as the starting position, and C14 as the initial speed,
each input the value 0. Next, in cell A14, input the following formula
=A14+dt
then copy-paste until cell A200. In cell D14, input the following formula a=g-
=g-(C14/vterm)^n
For the cells below it up to D200, input the following formula:
=g*(1-(C15/vterm)^n)
Next, in cell B15, input the following formula
=B14+(C14+C15)*s/2
then copy-paste until cell A200. In cell C15, input the following formula:
=C14+D14*dt
then copy-paste until cell A200.

Particle Energy in a One-Dimensional (1-D) Box

Suppose a particle is free to move in a one-dimensional region. Schrodinger's Equation,
ℏ2 𝑑 2 𝜓(𝑥 )
− = 𝐸𝜓(𝑥 )
2𝑚 𝑑𝑥 2
The solution to this equation is
𝜓 = 𝐴𝑒 𝑖𝑘𝑥
√2𝑚𝐸
with𝑘 = ± . The general solution of this equation is
ℏ
𝜓 = 𝐴𝑒 𝑖𝑘𝑥 + 𝐵𝑒 −𝑖𝑘𝑥
where A and B are arbitrary constants, determined by applying the boundary conditions.
Suppose that the particle is confined to the region 𝑥 = 0 dan 𝑥 = 𝑎. Next, the potential 𝑉 (𝑥 ) =
0 for 0 ≤ 𝑥 ≤ 𝑎 and 𝑉 (𝑥 ) = ∞for the others. The time-independent Schrodinger equation is
given by
ℏ2 𝑑 2 𝜓(𝑥 )
− + 𝑉 (𝑥 )𝜓(𝑥 ) = 𝐸𝜓(𝑥 )
2𝑚 𝑑𝑥 2
For the area outside the box, 𝑉 (𝑥 ) = ∞ and 𝜓(𝑥 ) = 0. For the area within the box, the time-
independent Schrodinger Equation is written as
ℏ2 𝑑 2 𝜓(𝑥)
= 𝐸𝜓(𝑥 )
2𝑚 𝑑𝑥 2
The boundary condition, 𝜓(0) = 𝜓(𝑎) = 0. General solution,
𝜓(𝑥 ) = 𝐴 sin 𝑘𝑥 + 𝐵 cos 𝑘𝑥
𝑛𝜋
At𝑥 = 0, 𝜓(0) = 𝐵 = 0. At𝑥 = 𝑎, 𝜓(𝑎) = 0, then𝐴 sin 𝑘𝑎 = 0or𝑘 = 𝑎 , here n is an integer. So,
the solution to the time-independent Schrodinger equation that satisfies the boundary
conditions is
𝑛𝜋
𝜓n (𝑥 ) = 𝐴 sin 𝑥
𝑎
2
with𝑛 = 1,2,3, ⋯. Using normalization conditions, the constant A is obtained𝐴 = √𝑎, so that

2 𝑛𝜋
𝜓n (𝑥 ) = √ sin 𝑥
𝑎 𝑎
(Griffiths & Schroeter, 2017).

To visualize the particle wave function in a one-dimensional box, enter the wave function
2 𝑛𝜋
formula 𝜑𝑛 (𝑥) = √𝑎 sin ( 𝑎 𝑥) and the probability |𝜑𝑛 (𝑥)|2 in cells D8 and E8 on an excel
worksheet.
=SQRT(2/$B$8)*SIN(RADIANS(($C$6*180*C8)/$B$8))

and
=ABS(D8^2)

Radioactive Decay
The rate of radioactive decay is given by
dN
Rate of Change =
= −𝜆𝑁
dt
ln 2
where 𝜆 = 𝑡 is the decay constant. By integrating the rate of change equation, it is
1
2
obtained(Halliday, Resnick, & Walker, 2010)
𝑁(𝑡) = 𝑁0 𝑒 −𝜆𝑡
If 𝑃(𝑡) = 1 − 𝑒 −𝜆𝑡 is the probability of decay, then we get𝜆 = − ln(1 − 𝑃).

3. RESULT AND DISCUSSION

Learning with spreadsheets is a learning tool in MS Excel with simple functions that can
produce simulations or visualizations of equations in physics. In visualization with
spreadsheets, constants or data can be replaced, and it will produce different visualizations
quickly.
Projectile Motion
In simulating projectile motion using a spreadsheet, we can obtain a graph that visualizes
Projectile’s position at any given time. We will show a projectile motion simulation with no air
resistance and with air resistance. In this case, the x and y positions of the projectile depend
on the initial velocity parameter. Changes to the initial speed and starting angle of the shot
can be made using the scrollbar.
In Figure 2, columns I and J are the values of x and y respectively, starting with a zero value,
i.e., from cells I5 to I119 and J5 to J119 which change at each time interval of 0.025 s. To
graph the x and y functions, using the values calculated in columns I and J, we must select

33
International Journal of Transdisciplinary Knowlegde

cells I5 through I119 and J5 through J119 and select the Insert Chart button. Next, we
choose a scatter graph and a scatter with smooth lines.
Figure 2 shows the graphs for the x and y positions for the predefined parameters and initial
conditions.

Figure 2. General Appearance of Projectile Motion Visualization

For the next step, create a ScrollBar on each Input Parameter of the projectile motion
visualization. To create a scrollbar, we will use the "Developer" menu. How to enable the
Developer menu:
1. Open the MS Excel application
2. Click the “Office Button” located in the upper left corner of the worksheet. Then select
Excel Options at the bottom.
3. After that, a dialog box will appear, select the popular section and put a checkmark (√)
on the “Show Developer Tab In The Ribbon”.
4. Then click OK.
To create a Scrollbar for the initial speed parameter. How to create a ScrollBar:
1. Click the Developer Tab
2. In the “Controls” group, click “Design Mode”. Then click "Insert" then select "Scroll bar
(ActiveX Controls).

After that, hover over cell F9 in the parameter deviation section, then drag it to the
right without releasing it until it looks like

Type the following code:

Private Sub ScrollBar1_Change()
' Initial conditions
Cells(7, 3) = ScrollBar1.Value / 0.2
End Sub
Description:
 ScrollBar1's text is that the scrollbar created is the first.
35
International Journal of Transdisciplinary Knowlegde

 The text “Initial speed” is the parameter to be varied.

 (7, 3) is the location of the value cell to be varied, with the meaning 7 is a row and 3 is
a column. In general, this value is in cell C7.
 The value of 0.2 is that the value to be displayed is a multiple of 5.
3. After that, click again "Design Mode".
Figure 3 and Figure 4 are respectively the results of visualizing the motion of a projectile with
a constant initial velocity and a different starting angle, and a different initial velocity and a
fixed or the same starting angle.

Figure 3. Visualization of projectile motion with different starting angle of shot 𝛉 = 𝟑𝟎𝟎 ,
𝛉 = 𝟒𝟓𝟎 , and 600
Figure 4. Visualization of projectile motion with velocity difference 𝛉 = 𝟑𝟎𝟎 , 𝐯 = 𝟐𝟓 m/s,
𝐯 = 𝟒𝟎 m/s, 𝛉 = 𝟑𝟎𝟎 , 𝐯 = 𝟏𝟎𝟎 m/s.

Based on Figure 3, it appears that the difference in the initial angle affects the horizontal
range with the same initial velocity of 17 m/s. A larger initial angle of 45 0 is greater than an
angle of 300. The same thing is also shown in Figure 3, which visualizes of projectile motion
with a constant difference in initial velocity and initial angle. The visualization results show
that the greater the initial velocity, the greater the horizontal range, and the maximum height.

Falling Objects in the Air

Figure 5 shows a general visualization display of free-fall motion with terminal velocity
vterm = 20 m/s. The resistance coefficient b is calculated using the formula m*g/vterm^2,
obtained 0.613125 kg/m. The value of this resistance coefficient will change with changes in
the terminal velocity vterm and the object's mass m.

37
International Journal of Transdisciplinary Knowlegde

Figure5. General view of free-fall motion visualization𝐯𝐭𝐞𝐫𝐦 = 𝟐𝟎 m/s

Figure 6 is the result of the visualization of free-fall motion with different terminal velocity
values, namely 5 m/s, 20 m/s, 45 m/s, and 75 m/s.
Figure 6. Visualization of free fall motion with different terminal velocities of 5 m/s;
20m/s; 45 m/s; 75 m/s; and 100 m/s.

Based on Figure 6, it appears that increasing the value of terminal velocity affects the graph
of position, velocity, and acceleration concerning a given function of time. Students can see
the change in the value in columns B13, C13, and D13. The results of the visualization using
a spreadsheet show that the larger the terminal velocity value, the more curved the graph of
the position versus time function, while the velocity and acceleration graphs tend to be linear.

Particle Energy in a One-Dimensional (1-D) Box

Figure 7 shows a graph of the wave function and probability with different values of n, i.e.,
𝑛 = 1 to 𝑛 = 4.
For𝑛 = 1, The wave function of the particle is
2 𝜋
𝜓1 = √ sin 𝑥
𝑎 𝑎
for 𝑛 = 2is

39
International Journal of Transdisciplinary Knowlegde

2 2𝜋
𝜓2 = √ sin 𝑥
𝑎 𝑎
for 𝑛 = 3is
2 3𝜋
𝜓3 = √ sin 𝑥
𝑎 𝑎
and 𝑛 = 4is
2 4𝜋
𝜓4 = √ sin 𝑥
𝑎 𝑎
Firgure 7. Graph of a wave function with𝒏 = 𝟏, 𝟐, 𝟑and 4.

Based on Figure 7, it appears that as the quantum number increases, the wave function
𝑎
forms oscillations. For 𝑛 = 2, the wave function is zero at 𝑥 = 2 which is called the node of
the wave function. For the case of particles in a one-dimensional box, the number of nodes is
𝑛 − 1. So, at 𝑛 = 1 there is no node, 𝑛 = 2 there is 1 node, 𝑛 = 3 there are 2 nodes, and 𝑛 =
4 there are 3 nodes, and so on. The probability of finding a wave-particle in the one-
dimensional region between 𝑥 = 0 to 𝑥 = 𝑎 is proportional to the square of the wave function.

Nuclear Radioactive Decay

Figure 8 shows the radioactive decay simulation result with different decay probability values.

41
International Journal of Transdisciplinary Knowlegde

Gambar 8. Grafik

Based on Figure 8, the decay time of the nucleus with the number 𝑁0 = 145, and the different
probability values of P show different graphs. The larger the value of P, the decay time of the
nucleus occurs faster than the value of small P.

4. CONCLUSION
Excel spreadsheets are very important in learning and understanding physics through
graphical visualization and simulations. In this article, we show how an excel spreadsheet is
used to create visualize of projectile motion, free-fall motion, particle wave functions in one-
dimensional squares, and radioactive decay. The results in this study indicate Spreadsheet
are very useful for effective physics learning, which can help students to learn physics
concepts effectively.
5. REFERENCES

Baker, J., & Sugden, J. S. (2007). Spreadsheets in Education –The First 25 Years.
Spreadsheets in Education (eJSiE), 1(1), 18-43.
González, M. I. (2018). Lens ray diagrams with a Spreadsheet. Phys. Educ., 53, 1-6.
Griffiths, D. J., & Schroeter, D. F. (2017). Introduction to Quantum Mechanics (Third Edition
ed.). Cambridge University Press.
Grigore, I., & Barna, E. S. (2015). Using Excel Spreadsheets to Study The Vertical Motion in
A Gravitational Field. Procedia - Social and Behavioral Sciences (hal. 2769 - 2775).
Elsevier.
Halliday, D., Resnick, R., & Walker, J. (2010). Fisika Dasar Edisi 7. Erlangga.
Katoch, S. K. (2020). MS-Excel Spreadsheet Applications in Introductory Under-Graduate
Physics-A Review. Journal of Science and Technology, 5(3), 48-52.
Oliveira, M., & Napoles, S. (2010). Using a Spreadsheet to Study the Oscillatory Movement
of a Mass-Spring System. Spreadsheets in Education (eJSiE), 3(3).

Singh, I., Kaur, B., & Khun, K. K. (2019). Simulating longitudinal vibration of coupled oscillator
using 4th order Runge-Kutta method by programming spreadsheet. European Journal
of Physics.
Squire, K., Barnett, M., Grant, J. M., & Higginbotham, T. (2004). Electromagnetism
Supercharged! Learning Physics with Digital Simulation Games Curriculum &
Instruction, School of Education. ICLS '04: Proceedings of the 6th international
conference on Learning sciences (hal. 513–520). ACM Digital Library.
Toback, D., Mershin, A., & Novikova, I. (2004). New Pedagogy for Using Internet-Based
Teaching Tools in Physics Course. Department of Physics (Texas A& M University,
USA).
Uddin, Z., Ahsanuddin, M., & Khan, D. A. (2017). Teaching physics using Microsoft Excel.
Phys. Educ, 52.
Warner, C. B., & Judge, G. (2001). Microsoft Excel(TM) as a tool for teaching basic statistics.
Teach. Psychol.

43