Computer Lab Help 4

This page is designed to provide helpful information about the laboratory questions. You will find more details in the Lab Manual that accompanies this course. Begin this lab and every lab by introducing yourself to your partner. Determine the times when you can meet together during the week before the lab is due at your next Lab session. You should start this lab and each lab by typing the name of each team member and your computer number on the Lab Cover Page (or a copy of it).

The first WeBWorK problem asks questions about this help page and should help you work through the Lab more smoothly. The first problem of the main WeBWorK lab study explores Trigonometric models using either the sine or cosine function. These functions are used to approximate the length of daylight or the average temperature for a particular city. The second problem uses the discrete Matlhusian growth model and an alternative time-dependent discrete Malthusian growth model to study the population of a particular country. The third problem examines a breathing model, which can be used for detection of certain pulmonary diseases.

Problem 1: You begin this problem by finding the date of the summer and winter solstices from the Navy tables provided in the hyperlink of the problem. These dates are used to determine the length of the longest and shortest days for your particular city (even though a date nearby might appear shorter because of rounding the times). Use the provided hyperlink to generate the length of all days for the calendar year. You will use the longest and shortest daylight times to give the best values of the parameters a, which is just the average of these times, and the amplitude b, which is the difference between the maximum length of daylight and the average amount of daylight. The frequency, w, is computed using the length of the year, which we take to be 365.2425 days. (Excel has an odd way to enter p, you type 'PI()'.) The phase shift, f, is based on the number of days from the beginning of the year to the time when the maximum amount of daylight occurs. This phase shift will also depend on where your particular trigonometric function has its maximum. You may want to consult how this is computed from the lecture notes. The easiest way to determine the day that a particular date occurs on is to start in one column in your Excel spreadsheet and type 01/01, which Excel will interpret as January 1. In the column beside that you insert 0. You pull down updating the dates and adding 1 to each cell below the 0, giving you the number of days after January 1 each date occurs. Most of the questions for the daylight part of the problem should be easy to answer using this information. You may find it interesting to see that the earliest and latest sunsets and sunrises do not occur on the shortest and longest days of the year due to the elliptical nature of Earth's orbit. 

The temperature part of this problem introduces you to the powerful tool in Excel called Solver. This problem finds the nonlinear least squares best fit of the model

to the temperature provided in the table. You want to use our classroom techniques to estimate the parameters A, B, and y. The value of w is the same as you used for the daylight part of the problem. (You may want to use the Excel command of AVERAGE to find a good approximation for A.) It is important to find good estimates or Excel's Solver could converge to the wrong value.

Begin this part of the problem by copying the data onto a new Excel Spreadsheet. For sake of clarity we assume the data are in Columns A and B. Insert a new column between these columns, which will be for the t values. The next step is to find the corresponding t values for the middle of each month. As before, you want to want to create a table of dates and times. (I would recommend doing this in cells below your table entries, say Column A for the dates starting around Cell A15 and Column B for the corresponding times, i.e., enter 1/1 in Cell A16 and 0 in Cell B16, then pull down until the end of the year.) This will easily give you the corresponding t values for the dates Jan 15, Feb 14, Mar 15, Apr 15, etc., which are needed to be entered in your Cells B2-B13for the time of the model.

The next step is to enter your parameters as Named Variables. In Cells G2-G5, enter the names you want for the parameters A, B, y, and w, then in the Cells H2-H5, enter the estimated values you obtained above (precise for w). Highlight all the Cells G2-H5, then from the Formulas menu, select the Create from Selection option. This will name your variables to be used in the model. We return to Column D, where we enter our model. In Cells D2, we type the model using the formula above. You can simply type '=', then click in Cell H2 to get A. Similarly, we continue typing the formula entering the parameters in the formula from the appropriate cell in Column H. The variable t is obtained by clicking in Cell B2. This formula is pulled down to give an estimated simulation of the data. In Column E, we compute the square error between Cell C2 (the data) and Cell D2 (the model) by typing '= (C2 - D2)^2'. We pull this formula down to the end of the data and in Cell E14 we calculate the sum of square errors by typing '=' and clicking on the S sign in the Menu header.

We next want to find the nonlinear least squares best fit of the Model to the data. This is found by letting Excel compute the Minimum of the sum of square errors. To find the least sum of square errors, we use Excel's Solver. (The first time, you have to add Solver. This is done by going to the File menu item and choosing "Options." Under "Options" select "Add-Ins" and check "Solver Add-in," then click on "Go." This should make Solver available on this computer for the future. Most computers will already have had this done by other students.) To use Solver, you go to the Data menu item, and Solver should appear on the right. Highlight the cell with the sum of square errors, then click on Solver under the Data menu. A window will pop up with the Set Objective cell being the one you just selected. You will need to check that you want to Min (short for minimize) this Objective Cell, then click in the window for By Changing Variable Cells and highlighting the cells H2:H4, which contain our model parameters, A, B, and y. (Do NOT include the cell for w.) When you are in Solver for Excel 2010, you want to make sure there is a check in the box labeled: Make Unconstrained Variables Non-negative. Finally, choose the Solve option, and Excel will automatically adjust your model parameters to minimize the least squares best fit. You should see the sum of square errors drop and the values for A, B, and y change. This is you new model nonlinear least square best fit to the data.

After you have these best parameters, you need the model for the entire year. Recall that we put the entire year in Cells A16-B380 with the t variable in Column B. Next to the t variable in Column B you enter the model equation using the cells in Column C with the model once again calling for the named parameters in cells H2:H5. This can be pulled down to give the temperature for any day of the year. The DT(t) can be readily computed in Column D by taking the difference of successive cells in Column C. The problem asks for several detailed pieces of information. For example, you are asked to find the maximum average temperature and what day that occurs. Excel has MAX and MIN commands that allow you to find the maximum or minimum value of a set of numbers. Let us assume that we want to place the maximum average temperature in Cell F16, then in that cell you type '= MAX(C16:C380)'. Next we want to find the date without manually searching through all the data. There is a simple programming command that allows us to search for where this maximum is inside the data set. In Cell E16, you type '= IF(C16 = $F$16,"XX"," ")'. (FYI, the $F$16 is an important way for Excel to fix a cell and not have it updated with a pull down.) Next we pull down this programming statement through the year, and the result is that row with the maximum temperature will have an XX appearing and all other cells will have a blank.

Problem 2: This problem asks you to repeat what was done in the notes with the U. S. census data to census data from another country. You will be producing similar graphs (see the graph titled "Growth Rate/Decade for U. S." and the following graph called "U. S. Population"), which should give you a guideline. (See the lecture notes (Discrete Dynamical Models).) This Lab Problem uses the starting date (1950) as t = 0.

Use the power of Excel to find your growth rates. Suppose that once again you have entered the dates in Column A (starting in A2) and the population values in Column B (starting in B2). Insert a column between the dates and the census data, then enter the adjusted times t with t measuring the years after 1950 (so t = 0 represents 1950). Pull down these data and times to cover the time period from 1950 to 2050 (with many dates obviously not having data but will be used in you simulation). You will use Columns D-I for simulating the models, computing sum of square errors, and percent errors.

Below the data and space for model simulation, you want Excel to compute the growth rates that will be used for your first graph and the information needed for the model simulations. You could label A14 as t, then fill in the appropriate values for the times, t = 0, 10,...40, in Column A for use with the appropriate growth rates, which you compute in Column B. Label in B14 k, then below this compute the growth rates by entering "= C3/C2 - 1" to let Excel compute the growth rate associated with t = 0. Pull this formula down to obtain the subsequent growth rates associated with their respective times. (Note: there is one fewer growth rates than census data, since these are ratios of the census data.) At the bottom of this Column B you can use Excel's AVERAGE command to find the average growth rate, which will be used in your discrete Malthusian growth model. For graphing purposes, you should simply copy this one value in Column C next to your computed growth rates. Next you graph Columns A-C. Use Excel's Trendline (Linear) to find the best staight line fit to your growth data, which becomes your k(t) for the nonautonomous model. Once again it is very important to adjust this model growth formula to have 5 or 6 significant figures. This graph is adjusted to look similar to the one in the class notes for the U. S. growth rate.

The next stage in this problem is the simulation of the discrete Malthusian growth model using the average growth rate, r, and the nonautonomous growth model using the growth function k(t) that you found with Trendline. In Column D you simulate the Discrete Malthusian growth model by using the 1950 population in D2 (assuming D1 contains a model label). In D3 you insert the formula for the Malthusian growth model "= (1 + r)*D2," where you insert your value of r. This formula is pulled down to give you the model simulation. You can use Columns E and F to compute the sum of square errors and the percent errors needed for the WeBWorK problem.

The last part of the problem (not counting the graphing part) is the simulation of the nonautonomous Malthusian growth model. Start this model similar to the discrete Malthusian growth model with a label in G1 and the 1950 population in G2. The nonautonomous Malthusian growth model uses k(t), which you found earlier and depends on the t values in Column B. In is very important that the formula you put in G3 has all values coming from the second row. That is, if k(t) = a + bt, where a and b are determined by Trendline, then the formula that you put in G3 is given by "= (1 + a + b*B2)*G2." Once again, this discrete dynamical model is readily simulated in Excel by simply pulling down this formula. As above, you use Columns H and I to compute the sum of square errors and the percent errors needed for the WeBWorK problem.

Problem 3: This problem looks at data on populations of two countries. You consider basic Malthusian growth models to find rate of growth, doubling times, and approximate when the populations might be equal in size. The calculations for this problem are similar to the types of calculations you might be expected to perform on an exam, so this problem (except for the graphs) might best be done using nothing more than a basic calculator.