This preview shows pages 1–3. Sign up to view the full content.
This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: CSE 1520.03 The Glade Computer Use: Fundamentals Laboratory Handbook Chapter 5: Recurrence Calculations Objectives This chapter focuses on using spreadsheets to build models of processes that change with time such as the calculation of a running total of some quantity, the compound growth of an investment, or the growth of a population, for example. Here is a partial list of what the lab covers: Further practice of good design techniques Using recurrence formulas Preparation To be able to complete this lab in a reasonable amount of time it is essential that you are properly prepared, You should: Read the whole of this lab very carefully Exercise 1- A Running Total Example Suppose you have a row (or column) of numbers and you want to have another row that displays the cumulative (running) total of the first, i.e. Row: 8 3 12 9 11 5 Cumulative total: 8 11 23 32 43 48 There are many applications of this calculating the balance in a bank account as deposits are made for example. Open the file Exercise 1 (Lab5_Ex1) in Support Files (Chapter 5) on the course website. The model contains a Comments worksheet and a worksheet called CumulativeData (Figure 5.1), which contains just one long row of numbers at the moment. You want to add another row that will contain the running total. Figure 5 .1 the Data worksheet of Exercise 1 5-1 The Glade CSE 1520.03 Computer Use: Fundamentals Laboratory Handbook Add the row label first and then name the 2 rows. Select the first cell in the Total row. Since this is the first one it should simply contain the first value in the Data row, so construct a formula that will calculate this result for the first cell. =Data The second cell in the total row should contain the sum of the first cell in that row and the second cell in the Data row. However, the formula =Total + Data will cause a circular reference. Since the formula is in the Total range, it refers to itself. To avoid circularity we cannot use the name Total . The formula should be: = B2 + Data Now select this second cell and fill it along the row. You should not be surprised to see that this formula produces the results we want. Examine a few of the formulas that were created when you filled along the row and make sure you understand why they produce the results we were aiming for. You have just seen the two elements required when constructing a recurrence formula initialising the first value and constructing the recurrence. You have also seen that this is the one time when its not possible to use a named range in a formula. Exercise 2 Bank Account Balance Open the file Exercise 2 (Lab5_Ex2) in Support files (Chapter 5) on the course website. The model contains a Comments worksheet and a worksheet called Account , which contains a balance brought forward (from a previous month perhaps) and lists of deposits and withdrawals. The aim is to add another column that shows the balance after each deposit or withdrawal transaction....
View Full Document
This note was uploaded on 12/09/2010 for the course CSE CSE 1520 taught by Professor Paul during the Fall '09 term at York University.
- Fall '09