Radioactive decay simulation (Excel)

Radioactive decay

In this simulation the Excel random generator function =RANDBETWEEN(1,10) will be used to decide randomly if a nuclei will decay or not. It's a bit difficult to explain so might be best just to follow the steps and you'll get the idea as we go along.

  • Start by adding the function =RANDBETWEEN(1,10) to a column of 100 cells.
  • This will put a random number between 1 and 10 into each cell. You can reset this number by pressing F9.
  • This number will be used to decide if a nucleus decays or not, if the random number is smaller than some chosen value it decays if its bigger it doesn't. So if you choose a small number the probability of decay is high but a low number gives a low probability, we will start with 4.
  • Write the number 4 in a cell A102.
  • Write the formula =IF(A1<$A$102,0,1) in cell B1. This will return a 0 if cell A1 is smaller than the number in A102 and a 1 if it's bigger, copy this formula to all cells in column B by dragging down. (If you don't include the $ signs the cell number of A102 when you drag the formula and you don't want it to).
  • The spreadsheet now includes the data for the first second. If the value is 1 the nucleus survives if 0 it decays.

  • Now you are going to repeat this 12 times to represent 12 s of radioactive decay. If you copy and paste the formula it will change so that it refers to the adjacent cell. So the formula in D1 is =IF(C1<$A$102,0,1) etc.
  • You will end up with a large grid with lots of numbers the but they are not connected. If a nucleus decays in the first second a 0 appears but a 1 could appear in the next column. To make sure we kill off the nuclei we are going to make another grid where we multiply the 1's and 0's. Once the nucleus decays the product will be zero.
  • Leave a gap of one column then write =B1 in the first cell of the column.
  • In the next column write =B1*D1, then B1*D1*F1 etc.
  • When you have completed the first row drag the formula down.
  • You now have a table full of 1's and 0's. As soon as a 0 appears the row becomes full of zeros. In the example below the first nucleus decayed after 2s, the second after 7s etc.

  • All you have to do now is sum the columns and you can plot a graph of the number of nuclei remaining after each second. Also add a first value of 100 as the starting point.
  • You can now insert line graph to view the decay curve but if you want to do some analysis it's best to move the data to LoggerPro.
  • To get the data into Loggerpro you need to change the row of sums into a column. This can be done by copy and pasting the values then copying again and transposing the row to a column.

Analysis of data

There are now many things you can try:

  • vary the probability of decay and see the affect on the decay curve
  • try with larger numbers of nuclei to see if curve is smoother
  • investigate the relationship between the decay constant and probability of decay
  • try with a wider range of random numbers
All materials on this website are for the exclusive use of teachers and students at subscribing schools for the period of their subscription. Any unauthorised copying or posting of materials on other websites is an infringement of our copyright and could result in your account being blocked and legal action being taken against you.