CPSC130 Computing With Spreadsheets Decision Making Week 3

  • Slides: 12
Download presentation
CPSC-130 Computing With Spreadsheets Decision Making Week 3 Brenda Vander Linden and Aaron Armstrong

CPSC-130 Computing With Spreadsheets Decision Making Week 3 Brenda Vander Linden and Aaron Armstrong

Decision Making Quality of decisions Ways Excel helps 9/29/2020 Brenda Vander Linden and Aaron

Decision Making Quality of decisions Ways Excel helps 9/29/2020 Brenda Vander Linden and Aaron Armstrong 2

Garbage In, Garbage Out Tale of James A. Cummings Inc. Input 9/29/2020 Processing Output

Garbage In, Garbage Out Tale of James A. Cummings Inc. Input 9/29/2020 Processing Output Bad Whatever Bad Good Good Brenda Vander Linden and Aaron Armstrong 3

Making Quality Decisions Excel is only a tool. Double check n n Input Processing

Making Quality Decisions Excel is only a tool. Double check n n Input Processing Good formatting helps Then and only then use the spreadsheet for decision making. 9/29/2020 Brenda Vander Linden and Aaron Armstrong 4

Ways Excel helps Functions Built in commands n n n 9/29/2020 Goal Seek Scenario

Ways Excel helps Functions Built in commands n n n 9/29/2020 Goal Seek Scenario Manager (Chapter 6) Solver (Appendix B) Brenda Vander Linden and Aaron Armstrong 5

Functions Predefined formulas n n Take arguments (parameters) as input Return some calculated value

Functions Predefined formulas n n Take arguments (parameters) as input Return some calculated value as output. Examples n n Sum Average Writing a function n 9/29/2020 Insert Function fx Icon Just type Brenda Vander Linden and Aaron Armstrong 6

Functions Continued In conjunction with cell ranges, allow addition/deletion of rows/columns Logic functions allow

Functions Continued In conjunction with cell ranges, allow addition/deletion of rows/columns Logic functions allow rudimentary programming Useful n n n 9/29/2020 Predefined: PMT is hard to write Smart: auto add inserted column/row Flexible: IF allows conditional behavior Brenda Vander Linden and Aaron Armstrong 7

Many functions available Math Abs, Log, Rand, Round, Sum Finance FV, Pmt Date Today,

Many functions available Math Abs, Log, Rand, Round, Sum Finance FV, Pmt Date Today, Now, Minute Text Find, Substitute, Trim, Upper Database DAverage, DCount, DMin Statistical Average, Rank, Stdev Logical If, And, Or, Not 9/29/2020 Brenda Vander Linden and Aaron Armstrong 8

IF If condition n n Then Value 1 Else Value 2 Example n =IF(A

IF If condition n n Then Value 1 Else Value 2 Example n =IF(A 1>60, “Pass”, “Fail”) Nesting Example n 9/29/2020 =IF(A 1>85, “Good”, IF(A 1>60, “Okay”, “Fail”)) Brenda Vander Linden and Aaron Armstrong 9

VLookup Looks up a value from a sorted table Parameters n n n Value

VLookup Looks up a value from a sorted table Parameters n n n Value to look up Table reference Column number =VLOOKUP(I 4, $I$20: $J$24, 2) 9/29/2020 Brenda Vander Linden and Aaron Armstrong 10

PMT and FV PMT: Periodic Payments n n n Interest rate Number of payments

PMT and FV PMT: Periodic Payments n n n Interest rate Number of payments Present value (negative for loans) FV: Future value of an investment n n n 9/29/2020 Interest rate Number of payments Payment made each period Brenda Vander Linden and Aaron Armstrong 11

Built-in Commands Hide Freeze panes Auto. Filter Sort Goal Seek 9/29/2020 Brenda Vander Linden

Built-in Commands Hide Freeze panes Auto. Filter Sort Goal Seek 9/29/2020 Brenda Vander Linden and Aaron Armstrong 12

CPSC130 Computing With Spreadsheets Week 5 Multiple WorksheetsCPSC130 Computing With Spreadsheets Week 5 Multiple Worksheets
Computing Spreadsheets Computing Year 56 Spreadsheets Ordering andComputing Spreadsheets Computing Year 56 Spreadsheets Ordering and
Computing Spreadsheets Year One Computing Year 56 SpreadsheetsComputing Spreadsheets Year One Computing Year 56 Spreadsheets
DECISION MAKING DECISION MAKING Decision Making is concernedDECISION MAKING DECISION MAKING Decision Making is concerned
Decision Making DECISION MAKING Decision making is theDecision Making DECISION MAKING Decision making is the
DECISION MAKING Decision Making Decision Making a choiceDECISION MAKING Decision Making Decision Making a choice
DECISION MAKING AND ORGANIZING DECISION MAKING Decision makingDECISION MAKING AND ORGANIZING DECISION MAKING Decision making
IS171 Computing With Spreadsheets Decision Making Week 4IS171 Computing With Spreadsheets Decision Making Week 4
IS 171 Computing with Spreadsheets Introduction to SpreadsheetsIS 171 Computing with Spreadsheets Introduction to Spreadsheets
CPSC 130 Computing with Spreadsheets Introduction to SpreadsheetsCPSC 130 Computing with Spreadsheets Introduction to Spreadsheets
Rational Decision Making SSEF 2 Decision Making DecisionRational Decision Making SSEF 2 Decision Making Decision