Naked Science Forum

Non Life Sciences => Physics, Astronomy & Cosmology => Topic started by: dgt20 on 02/03/2018 02:54:39

Title: How do you use decay constant formula to find decay in excel?
Post by: dgt20 on 02/03/2018 02:54:39
I want to compare my decay results using the dice model 1;6 chance experiment and prove that it is not accurate to real life representation of decay. Is it possible to do this by using the decay constant through excel? If so how would you set up the formula on excel?
Title: Re: How do you use decay constant formula to find decay in excel?
Post by: chris on 02/03/2018 07:29:01
The formula for the number of decayable nuclei Nt remaining after time t is given by:

      Nt = N0e-λt

...where N0 is the number of nuclei you are starting with and λ is the decay constant with units s-1

So you can write a simple recurring formula that substitutes for t over the range of values that you want to test.

Does that help?
Title: Re: How do you use decay constant formula to find decay in excel?
Post by: dgt20 on 02/03/2018 08:17:15
The formula for the number of decayable nuclei Nt remaining after time t is given by:

      Nt = N0e-λt

...where N0 is the number of nuclei you are starting with and λ is the decay constant with units s-1

So you can write a simple recurring formula that substitutes for t over the range of values that you want to test.

Does that help?

I understand the equation however i don't know how to plug in the decay constant in excel? I  just dont know how to graph/do this on excel.
Title: Re: How do you use decay constant formula to find decay in excel?
Post by: Bored chemist on 02/03/2018 14:23:24
Are you trying to replicate the dice experiment in excel?
Title: Re: How do you use decay constant formula to find decay in excel?
Post by: dgt20 on 02/03/2018 23:35:25
Are you trying to replicate the dice experiment in excel?

I want to compare decay results from dice experiment to decay using the decay constant on excel
Title: Re: How do you use decay constant formula to find decay in excel?
Post by: evan_au on 03/03/2018 10:24:36
Ok, I've had a go...

I simulate 16 dice -
- Each row represents one half life, numbered in Column A
- Column B shows the theoretical number present after this many half-lives. Note that this number becomes a fraction, while real dice can't become a fraction.
- if the dice face shows a number > 3, it continues into the next round.
- Column C counts how many dice are left
- Press F9 to recalculate (start with the full number of dice again)
- The results are slightly different every time you press F9
- You can experiment with more dice by duplicating column S in more columns to the right.
- There is a similar experiment on the "Coin" worksheet, simulating flipping a lot of coins. Only half of them are left after a half-life.

But I can't upload EXCEL files.
Download the file to disk, then remove the JPG suffix.
Scan with a virus scanner. Then it should open in EXCEL.
* Dice_Sim.xlsx.jpg (26.99 kB - downloaded 163 times)
Title: Re: How do you use decay constant formula to find decay in excel?
Post by: dgt20 on 03/03/2018 10:45:04
Ok, I've had a go...

I simulate 16 dice -
- Each row represents one half life, numbered in Column A
- Column B shows the theoretical number present after this many half-lives. Note that this number becomes a fraction, while real dice can't become a fraction.
- if the dice face shows a number > 3, it continues into the next round.
- Column C counts how many dice are left
- Press F9 to recalculate (start with the full number of dice again)
- The results are slightly different every time you press F9
- You can experiment with more dice by duplicating column S in more columns to the right.
- There is a similar experiment on the "Coin" worksheet, simulating flipping a lot of coins. Only half of them are left after a half-life.

But I can't upload EXCEL files.
Download the file to disk, then remove the JPG suffix.
Scan with a virus scanner. Then it should open in EXCEL.
* Dice_Sim.xlsx.jpg (26.99 kB - downloaded 163 times)

edit: nvm
Title: Re: How do you use decay constant formula to find decay in excel?
Post by: chris on 03/03/2018 11:01:14
But I can't upload EXCEL files.

Hi @evan_au - I've not tried it with Excel, but it should let you send files as attachments in PMs - see this upgrade note: https://www.thenakedscientists.com/forum/index.php?topic=70438.msg517348#msg517348

Let me know if it works please.
Title: Re: How do you use decay constant formula to find decay in excel?
Post by: alancalverd on 03/03/2018 11:26:28
Define your decay constant L

Put your starting number into a cell, say B2. I always start with B2 to give me space for annotations.

In B3 write    =B2*exp(-L)

Now use autofill to give values for B4.....BN where N is as large as you like.

You can calculate the half life t = ln2/L = 0.693/L and compare it with the graphical value.

The half-life or decay constant you use depends on how you want to reject the dice, usually either 1/6 per throw or 1/2 per throw. 1/6 will give you a smoother curve and a better fit between experiment and calculation - just like real life where measuring short halflives is much more difficult!.
Title: Re: How do you use decay constant formula to find decay in excel?
Post by: chris on 03/03/2018 15:51:00
But I can't upload EXCEL files.

Hi @evan_au - I've not tried it with Excel, but it should let you send files as attachments in PMs - see this upgrade note: https://www.thenakedscientists.com/forum/index.php?topic=70438.msg517348#msg517348

Let me know if it works please.
@evan_au  - since posting, I've checked the PM attachments settings and .xls and .xlsx files are both permitted in PM attachments.

Obviously, users should always screen attachments before they execute things on their local machine.
Title: Re: How do you use decay constant formula to find decay in excel?
Post by: evan_au on 04/03/2018 09:20:16
The "trick" of putting a dummy "JPG" suffix should work in delivering the EXCEL file via a post.

I tried putting an attachment on a PM (Personal Message) like the one in the link, and it didn't bring up the "Choose File" button?
 [ Invalid Attachment ]  [ Invalid Attachment ]

Am I looking in the wrong place?
Title: Re: How do you use decay constant formula to find decay in excel?
Post by: dgt20 on 04/03/2018 11:43:22
Define your decay constant L

Put your starting number into a cell, say B2. I always start with B2 to give me space for annotations.

In B3 write    =B2*exp(-L)

Now use autofill to give values for B4.....BN where N is as large as you like.

You can calculate the half life t = ln2/L = 0.693/L and compare it with the graphical value.

The half-life or decay constant you use depends on how you want to reject the dice, usually either 1/6 per throw or 1/2 per throw. 1/6 will give you a smoother curve and a better fit between experiment and calculation - just like real life where measuring short halflives is much more difficult!.

Using decay formula       Nt = N0e-λt, I replaced decay constant with 0.166 (dice 1/6 chance) then compared to the results of the formula N=1000(1-1/6)^t (time). Would this be a fair comparison of expected dice vs expect dice decay results?
Title: Re: How do you use decay constant formula to find decay in excel?
Post by: alancalverd on 04/03/2018 13:42:04
No.

N=1000(1-1/6)^t
is rearranged as

N = 833.33t, which increases with t and is very different from N = 1000/eλt,  which decreases with t.

If you use the Excel algorithm I suggested above, Np = ROUND (Np-1* 0.833, 0) you will get a succession of integers that look like idealised dice and behave very much like the decay of 100000 nuclei. Real dice will give you a rougher curve because you may get any number of sixes on a single throw, but we get the same effect with very small (10000 or less) numbers of real nuclei due to Geiger counter dead time limitations.
Title: Re: How do you use decay constant formula to find decay in excel?
Post by: chris on 05/03/2018 15:59:26
The "trick" of putting a dummy "JPG" suffix should work in delivering the EXCEL file via a post.

I tried putting an attachment on a PM (Personal Message) like the one in the link, and it didn't bring up the "Choose File" button?

* Send_Message_Chrome.PNG (47.47 kB . 921x602 - viewed 2930 times)
* Send_Message_Edge.PNG (33.91 kB . 781x506 - viewed 2931 times)

Am I looking in the wrong place?

Hello Evan - I've reset the permissions for the application. It might be that they had been suspended when the forum was rebuilt in January.

I've tested successfully, I think, PM'ing you a picture and a spreadsheet.

The file selection / attachment box now appears below the PM message body input box:


* screen_shot.PNG (49.33 kB . 1152x779 - viewed 2840 times)