- Details
- Parent Category: Programming Assignments' Solutions

# We Helped With This Excel Programming Homework: Have A Similar One?

Category | Programming |
---|---|

Subject | Excel |

Difficulty | Undergraduate |

Status | Solved |

More Info | Online Excel Help |

## Short Assignment Requirements

## Assignment Description

In this assignment you will simulate the process of population decay by using random numbers. We can make random numbers simulate an event which can occur with any probability that we decide. In this assignment we will use random numbers to simulate the population decay process.

The population decay process is founds in many fields of Science and Engineering and can be stated as follows. Given an initial population with a certain probability of decay per unit time of a member of that population, then that population will decline in an exponential fashion. The decay constants are dependent on the probabilty of decay. Thus

** **** N=N**_{o}**e**^{-at }^{ }^{ }

Where No is the initial number and N is the number remaining at time t.
'a is called the decay constant.

Some common examples of this process are as follows:

In **radioactive decay**,
Uranium atoms spontaneously decay with a certain probability per unit time. The
amount of Uranium present decays exponentially. Because the probability of the
fission of a particular uranium atom in a given time is small, the decay
constant is small.

In** Biology** given
an initial population of rabbits whose chance of getting eaten by a predator is
say one in five per week, then the population will decline exponentially. Here
no account is taken of production of new rabbits.

In** manufacturing **of
light bulbs (or any other components), if the probability of failure of
the lightbulbs are say 1

in four per year, then the population of light bulbs (with no replacements)
will decline exponentially.

**The assignment consists of
simulating the decay process in the following way:**

1)Use the **=RAND( )** function
to generate an event with a 75% probablity of survival and a
25% probability of failure. Since Random numbers are generated in the
range 0-1, this can be done by choosing "survival" if the
random number falls between 0- 0.75 and "failure"
if the random number falls between 0.75 and 1.0.

2) To model the process, for
each sample, generate random numbers until say a "failure" is
achieved which indicates decay of that sample. So if it takes 10 random
numbers to get a **"failure"** the individual has lived
10 units of time. This is repeated over and over again generating a
"history" for a reasonable sample size of individuals.

3) The simulation consists of
many repetitions to build up statistics of the lifetimes of the many
individual samples. You build up statistics of how many individuals lived for 1
unit of time, how many for 2 units of time how many for 3 units etc. At the end
of the simulations you have the history of a large number of individuals and
one can construct a graph of number left vs time. This will look like
an exponential decay. The equation is N=N_{0}e^{-at},
where **N _{0}** is the initial number at time 0 and

**"a"**is the decay constant. Note that the half life of the decay is the time when the number has dropped to one half of

**N**and is related to the constant

_{0}**'a'**by the formula

**Half Life = ln(2)/a.**

4)To get the constant you do a
semi log plot** ln(N(t)) vs t** which
should yield points roughly in a straight line. Use the trend line tool in
Excel to get the best" straight line fit and thus get the constant.

On a single graph show the ln(N(t)) vs time and the straight line produced by using the constants provided by the trend line tool.

__ASSIGNMENT DETAILS__

Note: that the file will take about a Megabyte of storage because we need many examples.

THE FOLLOWING COLUMN HEADINGS
MAY BE USED:

- Column 0: The
**"toss" number**, numbered one to 500 or one to 1000.**Use more a few thousand.** - Column 1: The
"toss" . Use the
**=RAND( )**function to fill about 2000 cells with either 0 which represents "survival" or 1 which represents "failure". There should be a**0**if the random number is between 0 and 0.75 and a**1**otherwise. This represents a 1 in 4 probability of failure in a time period. - You should have a cell to represent the number of samples.
- Column 2 and 3: Using a running count, generate the lifetime of the sample.(This will be illustrated in class). When "failure" has occurred the lifetime should be reset to 1. In column 3 isolate just the individual lifetimes and use this to calculate the average lifetime.
- Use the countif function or the frequency Excel function to find how many individuals "failed" after 1 period, 2 periods,........10 periods.
- Use the results to generate a
**summary table**as follows**.**

__THE SUMMARY TABLE.__

Your summary table should tabulate:

- time periods from 0 through 10
- The number of samples that have failed after that amount of time.
- The number of samples
remaining
**N(t)**. - The
**ln ( N(t))**to get a semi log plot - The constant
**"a"**generated by the regression analysis of the semi-log plot. - The average lifetime

__Summary of Graphs__

Generate a plot of **N(t) vs t**
(This should look like an exponential decay curve).

Generate a second graph
of experimental **ln (N(t))** and calculated **ln(
N(t))** vs** t ** Show the points only for the
"experimental values" and show the line only for the calculated
values which you have plotted from the regression data.

Clearly show values for the
decay constant,** a**; the half life (calculated from **"a"**)
; the average life.

**Further explanation will be
provided in class**

5
points for column1.

10 points for column 2

5 points for column 3.

5 points for each for the 4 calculated items in the summary tables.

10 points for the graphs, decay constant, half life.

10 points for appearance and organization

Total 60 points