Chapter 9 Spreadsheet (Part 1) PDF

Spreadsheet Chapter 9 Part 1 Topics to be covered  History of Spreadsheets  VisiCalc on Apple II (1979)  IBM’s Lotus 1-2-3 on DOS (1983)  Microsoft Excel / Apple Numbers (1987) 3  Common Uses  track personal budget/expenses  business...

Spreadsheet Chapter 9 Part 1 Topics to be covered  History of Spreadsheets  VisiCalc on Apple II (1979)  IBM’s Lotus 1-2-3 on DOS (1983)  Microsoft Excel / Apple Numbers (1987) 3  Common Uses  track personal budget/expenses  business profit & loss/payrolls  financial modelling and simulation  stock analysis and others… 4  Example: Profit & Loss 5 What is a spreadsheet ? ▰ A spreadsheet is an electronic worksheet used to manage and manipulate data arranged in columns and rows. 9.1.1 Cell and Range Address Some terminology Cell addresses : ‘A2’, ‘B1’, ‘C4’ etc Range addresses : ‘A1:C3’, ‘B5:B9’, ‘E2:G2’ ‘F8:F8’ 9.1.2 Formulas 9.1.3 Relative cell references Cell reference - how cell values are related to each other by specifying the column and row of another cell. 9.1.3 Relative cell references 9.1.3 Relative cell references Cell reference - how cell values are related to each other by specifying the column and row of another cell. 9.1.4 Absolute cell references Absolute cell references are used when we do not want the cell references in formulas to change when copied to other cells. To make an absolute cell reference, type a dollar sign ($) before the column letter or row number that should not change. This indicates that the particular column letter or row number will not be automatically changed when the formula is copied into other cells. 9.1.5 Mixed cell references To prevent the row number 1 from changing in the cell reference “C1”, use the absolute cell reference “C$1” instead. The dollar sign in front of “1” indicates that it will not be automatically changed when the formula is copied into other cells. 9.1.6 Range references Description of multiple cells that can be used in a spreadsheet formula. For instance, “A1:C3” is a relative range reference while “$A$1:$C$3” is an absolute range reference. 9.1.7 Automatic Recalculation = D2 * 3 9.1.7 Automatic Recalculation = $C$1 *(1 + B4) What happen if you do not use absolute cell reference for the cell C1 ? 9.2 Logical Operators and Functions Operators Logical functions 9.2 Logical Operators and Functions Logical functions 9.2 Logical Operators and Functions Use of ‘AND’ function 9.2 Logical Operators and Functions Use of ‘OR’ function 9.2 Logical Operators and Functions Use of ‘OR’ function Use of ‘NOT’ function 9.2 Logical Operators and Functions Use of nested ‘IF’ Quick Check 9.2 Quick Check 9.2 Q2). The following spreadsheet is used to track the number of tickets for an event ordered by students in a class. Students who order several tickets equal to or greater than the value in cell C1 get a free gift, which is supposed to be indicated by “Y” or “N” in C4:C17. Suggest a formula for cell C4 that can be copied to range C5:C17 in order to complete the spreadsheet correctly. Try it out ! Refer to the file Quickchk_9.2(student). xls Quick Check 9.2 Q3) The formulas below are two ways of comparing the value in cell C1 with the number 3. They both return a text value of “Less than 3”, “Equal to 3” or “Greater than 3”. =IF(C1