Document Sample

Using a spreadsheet to solve a basic numerical integration problem Prepared by Professor Mark Parnis, Sept 2003 Many problems that involve differential rate expressions can be solved without calculus using spreadsheets. An example that will illustrate this approach is the decay of a radioactive element. Suppose we have 10 grams of a material with a half-life of 30 minutes. We can use a spreadsheet to calculate and plot the amount of material that remains at any point in time. Let’s look at decay over 5 half lives, i.e., over 150 minutes. The proper differential expression for first-order decay is given by: d[ X ] = −k[ X ] dt Since, for a first-order process, ln(2) k= t1 / 2 we can calculate k = ln(2)/(30*60)=3.85x10-4 s-1 or, in minutes: k = ln(2)/30 = 0.0231 min-1 Note that, since first-order decay has a rate constant that is independent of concentration of matter, we can use whatever units we like to express concentration. Here we will use mass (effectively mass per unit of material). We could solve this directly for an analytical expression relating concentration of [X] to time at any point in time, i.e. use the integrated form of the rate law, [ X ]t = [ X ]0 e − kt Here, we will instead use the numerical integration approach with a spreadsheet to illustrate what you can do with a differential expression when you can’t conveniently integrate it. Open a spreadsheet and put in column titles of the various quantities of interest in the first cell at the top of columns. We are interested in [X]t and t, from 0 to 150 minutes. To begin, we need to enter the initial values for mass and time. At time t=0, we have 10 grams of material. Time Mass 0 10 Next, decide on the time interval over which you want to numerically integrate. Our time range is 150 minutes, which is 150*60=9000 seconds. Generally, the plot will look crude and non-continuous if you use fewer than around 100 data points, but you can afford to use many more since computers are very patient. Let’s choose a time interval of 1 minute and see how it looks, hence we will have 150 time units. To do this, enter in the second row of the time column the value 1.0, representing 1 minute. Then use the spreadsheet to calculate a new mass for the material, assuming 1 minute of decay. To do this, you need the following equation, and underlying logic: In minutes, the equation in question is: d[ X ] = −0.0231[ X ] dt however, since we are using it over a finite time interval, we should rewrite it as: ∆[ X ] = −0.0231[ X ] ∆t This is easily rearranged to yield: ∆[ X ] = −0.0231* [ X ] * ∆t The amount that remains after one time interval of ∆t=1 minute is given by: [ X ]t + ∆t = [ X ]t + ∆[ X ] or [ X ]t + ∆t = [ X ]t + (−0.0231* [ X ]t * ∆t ) Finally, rearranging a bit: [ X ]t + ∆t = [ X ]t * (1 − 0.0231* ∆t ) = [ X ]t * 0.9769 * ∆t Using this general expression, we find that, starting with an initial mass of 10 grams, we have after 1 minute: [ X ]t + ∆t = 10.0 * (0.9769) = 9.769 To do this in the spreadsheet, we need to note that the equation we’re using has only the concentration at time t as the variable. This is in column B, line 2 for the first calculation. Therefore our equation will reference that cell to get the value of [X]0 = 10 for the calculation. Insert the following equation into the cell for the mass at time t=1.0 minute, B3: =B2*0.9769 The spreadsheet will calculate the value 9.769 automatically. Time Mass 0 10 1 9.769 The rest is rather trivial. To establish the time data, highlight the first two time data cells, containing 0 and 1.0 respectively. Click and hold the little black square in the bottom of the two highlighted cells and drag that box down the column to cell 151 to get 150 values. Having two cells in the group of highlighted data tells the spreadsheet what the interval is between data points, with the default being 1 if only one cell is highlighted. Then click on the little black square on the bottom of the B3 cell, and drag the highlight down the column to cover all the cells in which you want to do the calculation. The spreadsheet automatically retains the “relative logic” of the calculation, understanding that the cell above the one in question is to be used as the source of [X]t for each of the 150 calculations needed. You should get something that looks like: Time Mass 0 10 1 9.769 2 9.543336 3 9.322885 4 9.107526 5 8.897143 6 8.691619 7 8.490842 8 8.294704 9 8.103096 10 7.915915 11 7.733057 12 7.554423 13 7.379916 Finally, prepare a plot of the mass versus time, with the selection of unsmoothed connection of each data point and data symbol suppression. You will arrive at a plot like: First-order decay 10 9 8 Mass at time t /grams 7 6 5 4 3 2 1 0 0 50 100 150 Time/ minutes Although the approach will be somewhat different for each problem, the basic logic to numerical integration remains the same: 1) Establish a differential expression relating the rate of change to some quantity. 2) Convert this to a finite difference expression, with deltas. 3) Develop an expression showing how the quantity in question changes after a small time interval has elapsed, the interval chosen to ensure that the change in the quantity is small compared to the quantity itself. 4) Use this expression repeatedly to develop a series of numbers that approximate the true value for the quantity at a given time. The more time intervals used, the better the approximation.

DOCUMENT INFO

Shared By:

Categories:

Tags:

Stats:

views: | 12 |

posted: | 3/26/2010 |

language: | English |

pages: | 4 |

OTHER DOCS BY housework

How are you planning on using Docstoc?
BUSINESS
PERSONAL

By registering with docstoc.com you agree to our
privacy policy and
terms of service, and to receive content and offer notifications.

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.