19 Pages
Unlock Document

Electrical Engineering and Computer Science
EECS 1520
David Langille

CSE 152003The GladeComputer Use FundamentalsLaboratory Handbook Chapter 5 Recurrence CalculationsObjectives This chapter focuses on using spreadsheets to build models of processes that change with timesuch 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 coversFurther practice of good design techniquesUsing recurrence formulasPreparation To be able to complete this lab in a reasonable amount of time it is essential that you are properly prepared You shouldRead the whole of this lab very carefullyExercise 1A 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 ieRow 8 3 12 9 11 5 Cumulative total 8 11 23 32 43 48There are many applications of thiscalculating the balance in a bank account as deposits are made for exampleOpen the file Exercise 1 Lab5Ex1 in Support Files Chapter 5 on the course website The model contains a Comments worksheet and a worksheet called CumulativeData Figure 51 which contains just one long row of numbers at the moment You want to add another row that will contain the running totalFigure 51the Data worksheet of Exercise 151 The GladeCSE 152003 Computer Use FundamentalsLaboratory 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 DataThe 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 formulaTotalDatawill 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 beB2DataNow 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 forYou have just seen the two elements required when constructing a recurrence formulainitialising 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 formulaExercise 2Bank Account Balance Open the file Exercise 2 Lab5Ex2 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 transactionEnter and format a title for the column define names for the existing data and begin to create the formula for the first cell of the new column This formula should be straightforward since you simply need to initialise the balance to that brought forwardThe formula for the second cell in the balance column must set up the recurrence calculation of the balance It is in effect simply the implementation ofBalanceprevious balancedepositwithdrawalBuild this formula yourself using the spreadsheet syntax and fill it down the column Since balance and previous balance are in the same range you need to use a cell reference for this part of the formula Note that the formula will work because Excel treats blank cells as if they contain zeroes If the deposit or withdrawal value is blank the formula will actually add or subtract zero52
More Less

Related notes for EECS 1520

Log In


Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.