Skip to content
BY-NC-ND 4.0 license Open Access Published by De Gruyter January 27, 2022

Simple and easy way for students to develop a dynamic model on Excel sheet

An Excel sheet to illustrate radioactive decay/equilibrium using a visual model which does not need knowledge of graphic programming or differential equations

Masahiro Kamata and Asuka Kamata

Abstract

Radioactive decay is not only important in the field of radiochemistry but also useful as a teaching material for chemical kinetics. Although differential equations are often used to explain how decay rate changes over time, there are many students even in college or university who are not very good at mathematics and have difficulty in solving differential equations. Those students are expected to appreciate institutive and schematic illustrations using Excel sheets. In this paper, a water and tank model to demonstrate how radionuclides decay and decrease over time is presented as an example of the model that the students can develop or rearrange by themselves. Therefore, only four arithmetical operations were used in the sheet, so that the students can easily grasp the basic concept of a decay curve or radioactive equilibrium even if they do not have great knowledge of differential equations. In addition, only “Record Macro” and built in “Charts” were used on the sheet, and therefore, no knowledge or skill in graphic programming, such as Visual Basic, is needed to make and use the sheet. A brief online survey indicated the model was interesting to high school students. Since Excel is widely used all over the world, the sheets we have developed can be used in many countries without additional expense.

Introduction

In the field of chemical kinetics, radioactive decay is a good material when teaching the first order reaction. In addition, in the field of radiochemistry, the amount change of radionuclides which decay in series is important because it is imperative for understanding radioactive equilibrium especially in natural radioactive decay series.

On the other hand, there are many computer programs for teaching how radionuclide decay over time (PhET, 2020). But most of them are so called ready-made software and students have little chance to make or to rearrange by themselves. As Excel sheet is very useful not only as a tool to analyse the results of students’ experiment but also as a platform for simulation, there are many works which use it for educational purposes (Andrews, 2009; Reid & McDonald, 2012). Although many students are familiar with Excel, the students who are not so good at mathematics tend to use the sheet as a black box. In addition, even if the students want to visualize their calculated results using some dynamic model, if they do not know much about graphic programming, what they can do on a spread sheet is rather limited. From such a perspective, simple and easy way to develop a dynamic model on Excel sheet will be presented below for the students who are not good at mathematics and programming.

Students learn about radiation and radioactivity in junior high school science and high school physics classes in Japan (MEXT, 2020a, 2020b). Although many people agree that radiation education is important not only in physics but also in other subjects such as environmental education and energy education, there are two main problems which make radiation education in school science or physics difficult. The first one is that educational practices such as measuring radiation or radioactivity at school are not easy to implement because the use of radioactive materials is strictly regulated by law, and students and their parents are nervous about the hazards of radiation. The second problem is the fact that differential equations are often used to explain how decay rate changes over time. Although the equations are not too complicated, there are many students even in college or university who are not very good at mathematics and have difficulty in solving the equations. In addition, just because there are students who can solve the differential equations does not mean that all of them have understood the basic concepts of the decay process, such as half-life and radioactive equilibrium.

As for the second problem, step-by-step calculation is educationally useful because the decay process is directly and simply expressed with only four arithmetical operations, which helps the students to grasp the physical meaning of the calculation in each step. The more detail explanations were reported by the author as well as other researchers (Kamata & Watanabe, 2000; Lingard, 2003; Severn, 1999). Visual illustration of the phenomenon is also very helpful for young students, but it is not easy for them to make elaborate models using graphic programming such as Visual Basic. In this work, only “Record Macro” and built in “Charts” and “Shapes” were used to draw a water and tank model, and knowledge and skill of graphic programming, such as Visual Basic, was not needed.

Methods

Calculation

When radionuclide A decays into B as;

(1) A B

the decay rate dN A /dt is expressed as;

(2) d N A / d t = λ A N A

where N A is the number of the radionuclide A and λ A is a decay constant of the radionuclide A, which can be expressed as log e 2/T 1/2A (=0.693/T 1/2A ) using the half-life of the nuclide T 1/2A .

When the produced nuclide B is also radioactive and decays into C as shown below;

(3) A B C

the change rates of radionuclides B, C are expressed as;

(4) d N B / d t = λ A N A λ B N B

(5) d N C / d t = λ B N B

By solving Eq. (1) under the initial condition N A = N A 0

(6) N A = N A 0 exp ( λ A t )

By solving Eqs. (1), (4), and (5) under the initial conditions as N A = N A 0 , N B  = N C  = 0, the following equations are obtained.

(7) N A = N A 0 exp ( λ A t )

(8) N B = N A 0 { λ A / ( λ B λ A ) exp ( λ A t ) λ A / ( λ B λ A ) exp ( λ B t ) }

(9) N C = N A 0 { 1 λ B / ( λ B λ A ) exp ( λ A t ) + λ A / ( λ B λ A ) exp ( λ B t ) }

As mentioned above, it is not too difficult to get analytical solutions (Eqs. (7)(9)) from Eqs. (2), (4) and (5). Numerical methods, e.g. the Runge-Kutta method, are also available for solving the differential equations. However, even if a student can plot a decay curve using analytical or numeral solutions, it does not mean that he has understood the basic concept of the decay process or radioactive equilibrium.

In this work, a step-by-step calculation method using an Excel sheet was adopted. In this method, each step reflects the fact that the number of radionuclides which decay in a short time Δt is proportional to the product of N A and Δt. Therefore, the number of A after Δt, can be expressed as N A  − λ A N A Δt. Since A decays into B, the number of decayed A is equal to the number of B produced from A. Therefore, N B increases by λ A N A Δt while it decays and decreases by λ B N B Δt during the same period (or in the same step). Although the results from this method are not so accurate, the differences to analytical solutions are within a few percent in most cases when the step size Δt is not too large (Reid & McDonald, 2012).

Water and tank model

The chart (e.g. decay curve) plotted using the calculated results mentioned above is very helpful for students to understand how the number of radionuclides changes over time. However, it is difficult for them to build up a mental image of the decaying process only through the chart.

In this paper, a model made of a tank and water was prepared as shown in Figure 1(A). The tank had a drain outlet on the bottom, and water running out through the outlet was assumed to be proportional to the height of the water* H in the tank and the size of the outlet a (cross-section area). Under this assumption, the change of the height of water can be expressed as;

(10) d H / d t a H

Figure 1: 
Water and tank model.

Figure 1:

Water and tank model.

H and a in Eq. (10) correspond to N and λ in Eq. (2), respectively. Therefore, the amount of water in the tank expressed as the height H presents the amount of radionuclide N, and water running out from the tank presents the decay of the radionuclide in the tank. The larger the outlet size, the more water comes out from the tank, which means that the larger the decay constant, the more radionuclides decay in unit time.

(*It should be noted here that the assumption “drained water is proportional to the height of water” holds only in the model and is not correct from the viewpoint of hydrodynamics. The drainage rate should be proportional to the square root of the height of water). The best point of a water and tank model is that tanks can be piled as shown in Figure 1(B), and the radioactive equilibrium in some decay series, such as 214Pb -> 214Bi -> 210Pb can be visually expressed.

In order to realise a visual model on a computer, some graphic programming such as Visual Basic is usually indispensable. Although most school teachers and students are familiar with Excel, there are still many who are not so familiar with Visual Basic and feel difficulty in using it. On the other hand, many kinds of charts are available with ready-made shapes in many colours. Therefore, we chose to express our models using a combination of a column/bar chart and ready-made shapes of rectangles. The column chart was used to express the level of water in a tank and the bar chart was used to express the size of a drain outlet. Detailed explanations are presented in Figure 2 with the formulas put in the cells.

Figure 2: 
Excel sheet for demonstrating how the model works. The Excel sheet presented in Figure 2 can be downloaded from following URL: https://drive.google.com/drive/folders/1FRtUMt9q35cQB9Cr6CAa0mtvVZ4VOfkh?usp=sharing.

Figure 2:

Excel sheet for demonstrating how the model works. The Excel sheet presented in Figure 2 can be downloaded from following URL: https://drive.google.com/drive/folders/1FRtUMt9q35cQB9Cr6CAa0mtvVZ4VOfkh?usp=sharing.

Since it is necessary to re-draw/paint charts and shapes repeatedly to make the water level in each tank and drain rate appear to change, Macro Record was used to re-draw/paint as shown in the same figure.

Excel sheets with typical results

Two kinds of Excel sheets were developed. The first one is presented in Figure 2, which was prepared to demonstrate how a water and tank model works. The size of the drain outlet changes according to the value of the decay constant (or half-life) of the radionuclide, and the level of the water descends over time (every time you press the [run one step] button). The second one is to show visually how radioactive equilibrium is established between a parent nuclide and its daughters. The sheet is presented in Figure 3.

Figure 3: 
Excel sheet for demonstrating radioactive equilibrium. The Excel sheet presented in Figure 3 can be downloaded from following URL: https://drive.google.com/drive/folders/1FRtUMt9q35cQB9Cr6CAa0mtvVZ4VOfkh?usp=sharing.

Figure 3:

Excel sheet for demonstrating radioactive equilibrium. The Excel sheet presented in Figure 3 can be downloaded from following URL: https://drive.google.com/drive/folders/1FRtUMt9q35cQB9Cr6CAa0mtvVZ4VOfkh?usp=sharing.

It should be noted here that when you use the sheets on Excel 2019, warning message “Negative or zero values cannot be plotted correctly on log charts …” is issued. You may neglect it or use negligibly small values such as 1.0E-6 instead of zero as initial values preset by Record Macro.

Sheet for demonstrating a water and tank model (cf. Figure 2)

First, press the [Init] button, and input the decay constant and time step size in C3 and G3, respectively. The Recorded Macro related to [Init] has a function to clear the cells B6:E105. For most students, half-life is more familiar and easier to grasp than the decay constant. So, if you input the decay constant of the nuclide whose half-life is, for example 30 min (=1800 s), you can input the formula “=0.693/1800” instead of the calculated value (3.85E-4) in cell C3. The size of the drain outlet changes according to the value of the decay constant λ. Since the outlet should not be too big compared with the bottom size of the tank, the half-life should be greater than 500.

Next, input the number of radionuclides at t = 0 in cell C6. Usually, just input 100. Then the number of radionuclides at t = Δt is calculated and expressed in cell C5, where the formula N A (t + Δt= N A (t λ A N A (t) Δt (=C6 − $C$3*C6*$G$3) is preset. Since the number of nuclide B produced from A is equal to the number of decayed radionuclides A, it is calculated as N B (t + Δt) = N B (t) + λ A N A (t) Δt (=D6 + $C$3*C6*$G$3) in cell D5.

The water level of the upper tank and lower tank is calculated using the values in C6 and D6, respectively.

Next, press [run one step], then the values in cells B5:E105 are copied to the corresponding cells one line below.

As shown in Figure 4, students can recognise the water level is decreasing according to the preset half-life, and the amount of water running out through the outlet also decreases as the water level drops. When the decay constant is changed, the size of the outlet as well as the water flow rate change, as shown in Figure 5.

Figure 4: 
Typical result by water and tank model for demonstrating how the model works.

Figure 4:

Typical result by water and tank model for demonstrating how the model works.

Figure 5: 
Relation between outlet size and drain rate.

Figure 5:

Relation between outlet size and drain rate.

Sheet for demonstrating radioactive equilibrium using a water and tank model (cf. Figure 3)

The decay series picked up on the sheet was made of three radionuclides:

A B C

where λ Α  = 0.693/10,000, λ Β  = 0.693/1000, λ C  = 0.693/500.

In the sheet, the model was depicted with three tanks, and the structure of each tank was similar to the one illustrated in Figure 1. However, the size of the drain outlet and water coming out through it were depicted fixed regardless of the decay constant and the water hight. Therefore, there were no restrictions on the magnitude of the decay constant. If the drain outlet of the third tank is not necessary (if the nuclide corresponding to the third tank is not radioactive), just make its decay constant zero (input zero in cell E3).

As shown in the previous sheet, press the [Init] button and input the decay constants in C3–E3, then input the time step size in G3. The Recorded Macro related to [Init] has a function to clear the cells B6:E105. Next, input the number of radionuclides A at t = 0 in cell C6. Then the number of radionuclides A at t = Δt is calculated and expressed in cell C5, where the following formula has been preset.

(11) N A ( t + Δ t ) = N A ( t ) λ A N A ( t ) Δ t ( = C 6 $ C $ 3 C 6 $ G $ 3 ) .

In cells D5 and E5, the numbers of the radionuclides B and C at t = Δt are calculated as

(12) N B ( t + Δ t ) = N B ( t ) λ B N B ( t ) Δ t + λ A N A ( t ) Δ t ( = D 6 $ D $ 3 D 6 $ G $ 3 + $ C $ 3 C 6 $ G $ 3 )

(13) N C ( t + Δ t ) = N C ( t ) λ C N C ( t ) Δ t + λ B N B ( t ) Δ t ( = E 6 $ E $ 3 E 6 $ G $ 3 + $ D $ 3 D 6 $ G $ 3 )

The water levels of the three tanks are calculated and expressed using the values in C6–E6. Then press [run one step] button, and the values in cells (B5:E105) are copied to the corresponding cells one line below. The numbers of nuclides at t = Δt are moved to the cells C6–E6 and those at t = 2Δt are calculated and expressed in cells C5–E5.

A typical result is presented in Figure 6. As shown in the figure, students can recognise how radioactive equilibrium is established by pressing [run one step] button repeatedly.

Figure 6: 
Typical result by water and tank model for demonstrating radioactive equilibrium.

Figure 6:

Typical result by water and tank model for demonstrating radioactive equilibrium.

Response from high school students

A simple sheet as shown in Figure 7 was prepared with a short video clip to explain how to make the tank model using Macro Record, Charts and Shapes in Excel. After showing the video to 10 high school students*, a brief questionnaire survey was conducted online to clarify whether the numeral calculation and graphic presentation using Excel can be actually accepted by the students.

Figure 7: 
A sheet to demonstrate Macro Record, Charts and Shapes using simple water tank.

Figure 7:

A sheet to demonstrate Macro Record, Charts and Shapes using simple water tank.

The questions and their answers are presented in Figure 8. Based on their answers for Q3 and 4, most of the students are considered to have understood the meaning of the step-by-step calculation. As for the Macro Record. most of the students did not feel difficulty in it as shown in their answers for Q5. The results for Q6 and 7 reveal the students’ strong interest in our new method, especially in graphic expression using Charts and Shapes in Excel.

Figure 8: 
Responses from high school stundents.

Figure 8:

Responses from high school stundents.

(* The number of the students are too few and their level is far above average. This is because we had to give up larger survey including several schools under the COVID-19 situation).

Conclusions

By using the Excel sheet with ready-made charts and shapes, it is possible to realise a visual model for educational purposes to help students grasp the basic concepts and/or acquire knowledge of the decay process of radionuclides. Since no knowledge of graphic programming such as Visual Basic is needed and differential equations are not used in the sheets, it is not difficult for high school teachers and students to make or rearrange the model by themselves.

The authors have reported simple experiments to observe radioactive equilibrium using mineral water (Onishi, Uono, & Kamata, 2019). The Excel sheet presented in this paper is expected to be useful in analysing the results of actual observations, as shown in Figure 9. Since Excel is widely used all over the world, the sheets we have developed can be used in many countries without additional expense.

Figure 9: 
Radioactive equilibrium between 222Rn and its daughters.
(β ray from charcoal activated was measured which adsorbed 222Rn expelled from mineral spring water (Kirara-no-yu). β ray was emitted from 214Pb and 214Bi which were produced from 222Rn).

Figure 9:

Radioactive equilibrium between 222Rn and its daughters.

(β ray from charcoal activated was measured which adsorbed 222Rn expelled from mineral spring water (Kirara-no-yu). β ray was emitted from 214Pb and 214Bi which were produced from 222Rn).


Corresponding author: Masahiro Kamata, Science Education, Tokyo Gakugei University, 4-1-1 Nukuikitamachi, Koganei-shi, Tokyo 184-8501, Japan, E-mail:

Acknowledgement

The basic idea of water and tank model using Excel was presented at NICE 2019 (Network for Inter-Asian Chemistry Educators) in Taiwan. It is essential that excellent files are added as supplementary material for readers to download and to use as astatine point.

  1. Author contributions: All the authors have accepted responsibility for the entire content of this submitted manuscript and approved submission.

  2. Research funding: None declared.

  3. Conflict of interest statement: The authors declare no conflicts of interest regarding this article.

References

Andrews, D. G. H. (2009). An Excel™ model of a radioactive series. Physics Education, 44(1), 48–52. https://doi.org/10.1088/0031-9120/44/1/007.Search in Google Scholar

Kamata, M., & Watanabe, C. (2000). Usage example of Microsoft Excel for radiation education. Radiation Education (in Japan), 4, 18–25.Search in Google Scholar

Lingard, M. (2003). Using spreadsheet modelling to teach about feedback in physics. Physics Education, 38(5), 418–422. https://doi.org/10.1088/0031-9120/38/5/306.Search in Google Scholar

MEXT: Ministry of Education, Culture, Sports, Science and Technology. (2020a). Course of study for junior high school science: p. 41, p. 64. Retrieved from https://www.mext.go.jp/component/a_menu/education/micro_detail/__icsFiles/afieldfile/2019/03/18/1387018_005.pdf.Search in Google Scholar

MEXT: Ministry of Education, Culture, Sports, Science and Technology. (2020b). Course of study for high school science: p. 57. Retrieved from https://www.mext.go.jp/content/1407073_06_1_2.pdf.Search in Google Scholar

Onishi, K., Uono, Y. & Kamata, M. (2019). A safe and easy experiment to measure natural radiation – using charcoal filter and underground water. Journal of Science Education in Japan, 43(4), 451–456. https://doi.org/10.14935/jssej.43.451.Search in Google Scholar

PhET: Interactive Simulations for Science and Math. (University of Colorado Boulder). (2020). Retrieved from https://phet.colorado.edu/en/search?q=decay.Search in Google Scholar

Reid, A., & McDonald, R. (2012). Simulating decay chains with spreadsheets. Physics Education, 47(1), 18–20. https://doi.org/10.1088/0031-9120/47/1/f08.Search in Google Scholar

Severn, J. (1999). Use of spreadsheets for demonstrating the solutions of simple differential equations. Physics Education, 34(6) 360–366. https://doi.org/10.1088/0031-9120/34/6/404.Search in Google Scholar

Received: 2020-12-05
Accepted: 2022-01-03
Published Online: 2022-01-27

© 2022 Masahiro Kamata and Asuka Kamata, published by De Gruyter, Berlin/Boston

This work is licensed under the Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License.