Transcript Excel
S5 Accuracy All Sections Today’s Topic Security Hardware & Software IS Basics Information Quality Types of IS Systems Development PowerPoint Spreadsheet Design Database Processing & Design Telecommu -nications Functional, Crossfunctional & Interorganizational E-commerce Excel Access Business Intelligence Spreadsheet Design Simplicity Layout Choices Efficiency includes Clarity Accuracy Formulas Functions Macros Verifiability Basic Formatting Conditional Formatting Freeze Panes Page Setup Sorting Charts implemented with Documentation Data Validation Protection Formulas Functions Define the accuracy goal. Apply the spreadsheet design goal of accuracy. SUM Function AVERAGE Function MIN & MAX Functions COUNT Function 3D Calculations Range Name Change Legend Change Axis Add Data Series ‘Other’ Pie Slice Know the order of operations. Select the correct function or construct the correct formula. (Choose a function over a formula.) Isolate assumptions. Check relative and absolute cell references. Double-check all calculations. Parentheses Multiplication and Division Addition and Subtraction 1. 6/2*4= 2. 3+2*2–1= 3. ( 2 * 5 ) + 15 / 5 = Formula ◦ created by you ◦ = A1 + A10 Function ◦ keyword defined by Microsoft ◦ =SUM(A1:D1) ◦ Functions are more flexible than formulas If you delete Row 5 =SUM(C3:C5) =B3+B4+B5+B6 =SUM(C3:C6) =cell reference Used to transfer data from one cell to another Benefit over copy & paste: redundant location is dynamic SUM AVERAGE MIN and MAX COUNT Store numbers in cells Write equations to point to cells containing numbers Assumption =E2+3 is incorrect =E2+H2 is correct How Do You Spend Your Study Time? How Do You Spend Your Day? Cell Address Reference Changes when Copied A1 Relative Vertically or Horizontally $A$1 Absolute Never $A1 Mixed Vertically A$1 Mixed Horizontally The cell addresses in the copied calculation may change. Any changes are made according to the direction and distance of the destination cell from the original source cell. Is the formula entered going to be copied? If so, which direction? If it’s copied vertically, do you want the row references to change? If it’s copied horizontally, do you want the column references to change? Do you want such a change to take place? Start Stop Copy Formula? Yes Yes Change Rows? Vertical Vertical or Horizontal? No Stop Yes Horizontal Change Columns? No No Enter $ before row numbers that should not change Enter $ before column letters that should not change =SUM(B2:D2) 1. 2. 3. Will you copy this function? If so, which direction: vertical or horizontal? If you copy vertically, Excel will automatically change all relative row references. Do you want those row references to change? How Do You Spend Your Study Time? What equations would you use to calculate ◦ ◦ ◦ ◦ Weighted Credits for each course Total Semester Credits Total Semester Weight Credits GPA Remember to: ◦ Choose a function over a formula ◦ Isolate assumptions ◦ Check relative and absolute cell references IF function displays one of two possible values depending on the outcome of a logical test Logical Test compares two things If the Logical Test equates to TRUE, the cell is filled with the True Value. If the Logical Test equates to FALSE, the cell is filled with the False Value. If a course requires more than 3 hours of study time per week, we will label it HARD. Three or less hours of study is labeled EASY. What cell is used for the logical test? What is the logical test? What is the true value? False value? 3 Use the IF function to assign Pass/Fail grades. =IF(V4>=.6,”P”,”F”)