Summer II 2005, Page 1 Lec-06COMP 4—Power Tools for the Mind1Power ToolsSpreadsheetsWhat we’ll cover for this lecture topic:– The paper worksheet ...– What are electronic spreadsheets?– Cells • cell references • cell contents– Formulas and functions– What are spreadsheets good for?– Relative and absolute cell referencing (critical!)• Demos interspersed as time permitsThe electronic spreadsheetThe paper worksheet• Worksheets were used in many businesses for inventory management and accounting.• Key feature: columns to separate different types of information (‘fields’).• Because columns sizes varied depending on the use, many formats were available. Accounting Ledgers (Items, debits, credits, tax info…)Inventory (Items, #in stock, supplier, wholesale $, retail $...)Payroll (Name, SSN, job title, mailing address, salary…)BOTTOM LINEBOTTOM LINE: Lots of paperwork and calculation!2Mid 70’s: Dan Bricklin & Bob Frankston--the story.COMP 4—Power Tools for the Mind3Power Tools• Each entry occupies a cell... It can hold:– numericvalues– alphanumeric text (character strings).• As labels; what about as data?– an entire formula. The real power!The electronic spreadsheet• “The most brilliant software invention of the microcomputer era … “ Neill GrahamBasically, it’s just a 2-dimensional table:Dept Oct Nov DecClassical $2,500 $2,700 $2,900Country $3,855 $3,500 $3,200Jazz $4,880 $4,200 $4,800TOTALS $11,235 $10,400 $10,900☺COMP 4—Power Tools for the Mind4Power Tools• Remember Charles Babbage?• Manual spreadsheets had lots of problems…– time-consuming to produce– difficult to edit and update– miscalculation errors–transcriptionerrors (recording wrong number)• With electronic spreadsheets…– calculations are automated (fast, reliable, & simple to modify!)– formatting can also be easily changedCOMP 4—Power Tools for the Mind5Power ToolsReferencing a Cell• In most electronic spreadsheets, columns are indexed by letters, and rows are indexed by numbers.•A cell is identified and referenced by writing its column letter(s) followed by its row number. E.g., cell D3 is highlighted in the above picture.ABCDE1234COMP 4—Power Tools for the Mind6Power Tools• To refer to a block of contiguous cells, specify a starting cell and ending cell separated by a colon. E.g., the marked region below is C2:E4.• Non-contiguous blocks of cells can be referenced using a list in which the references are separated by commas, e.g., the reference C2:E4,A1:A3 is the marked block above along with cells A1, A2 & A3.ABCDE1234Referencing a group of CellsSummer II 2005, Page 2 Lec-06COMP 4—Power Tools for the Mind7Power Tools• So what’s it good for?– Personal/professional budgets and budget scenarios– Building space allocation– Grades– Income statements, balance sheets– Investment analyses– Keeping track of nearly anything...all cell functions that use that cell’s value are automaticallyrecalculated. Immensely USEFUL!When a cell value CHANGES….COMP 4—Power Tools for the Mind8Power Tools• Great for “what-if” analyses:–What if….• we decrease costs of good sold by 5%, what will our net earnings be?• I earn an 80% on Exam 2, what will my average be?• If my pay gets raised 5%, how much extra take-home pay will I get? Can I afford another loan?Demo 1 & 2…..Meet Microsoft ExcelCOMP 4—Power Tools for the Mind9Power ToolsExcel uses a very simple programming language:More about formulascell references (names)Digression…really interested in?cell contents• Nouns?• Basic Verb Set (2-operand operations) includes:Arithmetic operators:+ – * / ^Comparison operators:= < <= > >= < >Boolean operators:And Or NotFunctions! Special symbols:$ , : ( )and:COMP 4—Power Tools for the Mind10Power ToolsFunctions• Many more operations available as functions.• Functions take a collection of cell references as input, e.g.., SUM(A1:A5,B7,C8:C10) calculates the sum of the values cells A1:A5, B7, and C8:C10.• A few particularly useful functions:– SUM: computes sum of all values– PRODUCT: computes product of all values– MAX: finds maximum value – MIN: finds minimum valueCOMP 4—Power Tools for the Mind11Power Tools• Fairly simple syntax for formulas.– AVERAGE: finds average value– COUNT: finds number of non-empty cells in the group that contain numerical data.– COUNTA: finds number of non-empty cells in group.– IF(condition,A,B): conditional formula. If condition is true, use formula A. Otherwise, use formula B. • Mathematical precedence rules apply (use parentheses)COMP 4—Power Tools for the Mind12Power Tools• Formulas can depend upon other cells (which themselves might be the result of otherformulas...). Powerful!= SUM(D4:D12) = AVERAGE(A2:A200)Each is like a little computer programthat lives in a cell. Notice the colon:Summer II 2005, Page 3 Lec-06Relative cell references •Howdoes Excel look at a cell reference in a formula?Excel interprets theformula in cell B4 this way:= (B1 + B2) * B3Entry bar: B4A B C12341031520230= (B1 + B2) * B3 [Enter]Relative to where formula resides!Find value stored3 rows up from formula cell, Add that to value stored2 rows up from formula cell, Multiply that to value stored1 row up from formula cell, Displayanswer.Note: formulas displayed.13SO WHAT? Why should you care? Because:What if I copy A4 to C5?What will C5 look like?12345A B C D246= A1 + B235720304051015--You will want to RE-USE formulas you create as OFTEN as possible! (Copy and Fill)--You must know HOW Excel will INTERPRET your request!Row & cell references will change!14= C + D2 3What about here? Very same ideaWhat if I copy C4 to A6?What will A6 look like?123456A B C D24635720304051015= C1 +C2 +C315=A +A +A3 4 5What about here?What if I copy C4 to C5?What will C5 look like?12345A B C D24635720304051015= C1 +C2 +C3Did we move columns?16=C +C +CBut we DIDmove ROWS!2 3 4COMP 4—Power Tools for the Mind17Power ToolsImportant Tricks of the Trade• When you COPY a formula from one cell to another, any RELATIVEreferences will follow:– That is: CELL REFERENCES will CHANGE in the targetcell, as just seen.• Placing the special symbol $ in