 |
EECS 1520 3.0 — Computer Use: FundamentalsWinter 2016 |
 |
HomeworkImportant Resources: Chapters: 0 1 2 3 4 5 6 7 8 9Chapter 5 Homework
Support Files
What to Submit
Don't forget to put your name and student number in the header, and worksheet tab name in the footer, of each worksheet in the checklist. Printing Very Wide SpreadsheetsThe Pepper Moth Exercise contains a worksheet with 500 rows. We do not want you to print all 500 rows. The first page is sufficient for us to see if the pattern of your recurrence calculation is correct. The formula view of the PopulationGrowth worksheet will likely need to be three pages wide to make it readable. If you take these three physical pages and place them side by side you get the equivalent of the "first page" of the worksheet. These three pages are all you should hand in for this worksheet. See the discussion regarding printing such worksheets in Lab 5 below. To print the first three (or whatever) pages across the width of your worksheet go to the "Sheets" tab in "Page Setup…" and under "Page order" select "Over, then down", and then when you "Print…" select a "print range" from page 1 to 3. Discussion, Hints & TipsExercise 2: As explained in Exercise 1, parts of recurrence calculations require explicit cell references because using named ranges would lead to circular references. In exercise 2, the implementation of: Balance = previous balance + deposit – withdrawal will use explicit cell references for the Balance and previous balance parts, but can still use named ranges for deposit and withdrawal. Building this formula is done in much the same way as in Exercise 1. The main difference is two columns of "data" and everything being organized in columns instead of rows. Exercise 3: This exercise is essentially a Compound Interest problem, but with a wrinkle… to create the illusion that it adjusts itself automatically to show only enough rows that match the number of Periods. You fill down a sufficient number of rows to cover the worst case, i.e. the largest number of Periods you expect to ever encounter. The formulas produce null strings, i.e. text with no characters ─ a blank, for the cells not needed, but there is a formula in those cells. The formula for the Period column is outlined in the middle of page 5-5. It is fairly straight forward to translate it into an Excel formula. "increment the Period value by 1" in the true_part of the IF is the recurrence calculation, just add 1 to the cell immediately above. The Capital column also uses an IF. It can be outlined as follows: =IF(ISNUMBER(cell to left),calculate interest and add to capital,"") more information on ISNUMBER can be found in Lab 4. Lastly, be careful how you calculate the length of the Term. It is =YEAR(maturity date) – YEAR(investment date) and then format it as a number. Warning: If you enter dates in the form xx/xx/xxxx, be very careful. Canada and the United States differ in the order of month and day. Remember that Excel is a product of MicroSoft, an American company. Exercise 4: This exercise looks horribly complicated, but it isn't really. It's just a matter of getting organized and giving the various cells in the Parameters worksheet and columns in the PopulationGrowth worksheets suitable named ranges. Once you've done that the formulas for the calculations in columns D to K will look exactly like those given on page 5-7 and 5-8. Columns A and B hold the recurrence calculations using explicit cell references and column C is also a named range calculation. Named ranges for the values in the Parameters worksheet should be a combination of the column and row headings. For example, cell B5 should be named Light_Reproduction_Rate, and so on. For the PopulationGrowth worksheet, give the columns names like: Light_Population (column A), Total (column C), Light_Survivor (column D), etc. By a column we mean a range starting in row 3 to, say, row 502, if 500 rows are desired. With these named ranges the formula you enter into cell D3 will look like: =Light_Population−Light_Population*Light_Predition_Rate The recurrence formula you enter in cell A4 looks like: =D3+E3−F3+J3−G3 which is how the "Next population" formula for light moths in the middle of page 6-7 is translated into Excel. Formatting the column headings and generating the graph are done as in earlier labs. A word warning: The Parameters worksheet (with graph) that you hand in should contain the parameter values you've chosen to reproduce the graph in figure 5.7. Finding these values is very difficult. We will accept any reasonable effort to obtain a graph that is somewhere between figure 5.6 and 5.7. Appreciate that doing a "what-if" analysis can be difficult. Exercise 5: This exercise is practically a rerun of the Pepper Moth simulation. A couple of things are new: In Exercise 5a you import some experimental data into Excel. This becomes part of Exercise 5b. Note that in figure 5.11 this experimental data has been placed in a worksheet named "ExperimentalData", but the worksheet name mysteriously changes to "ThyroxinData" in figure 5.13. And there are some new wrinkles in creating the graph. The six "series" (curves) you plot come from two worksheets. In step 2 of the Chart Wizard (Excel 2003), under the "Series" tab you'll find boxes labeled "X Values" and "Y Values". The "Y Values" are the curve and the "X Values" are the x- or time-axis. These "X Values" come from column A of the same worksheet as the "Y Values". Notice these are different on the Simulation worksheet and the ThyroxinData worksheet. Use values from the same worksheet for the X and Y Values, otherwise the curves won't line up properly. After you are done creating the chart, you will have six curves of markers. Now you turn the three that came from the Simulation worksheet into lines. Double-click on any marker from the desired curve. A dialog box comes up that let's you change it to a line. Similar issues arise in Excel 2007, but there the chart is built piece-by-piece, so drawing data from two separate worksheets is more straightforward. Next comes the fun part: changing the three rates on the parameters worksheet so that the lines (the simulation) match the related markers (the corresponding experimental data). As with the Pepper Moth Simulation, do your best to reproduce figure 5.14, but you'll find it a similarly frustrating experience. |