Lab 2 Data Evaluation & Validation Excel Scenarios & Functions Simon Chapter 2 & 3

1. Lab 2Data Evaluation & ValidationExcel Scenarios & FunctionsSimon Chapter 2 & 3 URBPL 5/6010: Urban Research University of Utah Pam Perlich – BUC 103 Craig Forster – BEHS 101

2. Files for this Class Session • Chapter 3 • ConditionalFormat.xls • Subtotal.xls • Filter.xls • Scenario.xls • Validation.xls • Chapter 4 • Autosum.xls • ChangeValues.xls • ConditionalFormula.xls

3. Conditional Formatting • This allows you to automatically designate a format for data that fit a particular condition. • Select a range of cells • Format => Conditional Formatting • For example, if the value is negative, make the text red • To remove formatting: • Format => Conditional Formatting => Delete • You can nest 3 conditional formats Simon, Chapter 3

4. Conditional Formatting(ConditionalFormat.xls) • Format => Conditional Formatting Simon, Chapter 3

5. Subtotals • Subtotals are useful when data are • formatted symmetrically • Dimensioned with multiple categories • This can be useful in many applications • Accounting • Data summary • Reports Simon, Chapter 3

6. Subtotals: Subtotal.xls • Select entire data block • Data => Subtotal=>By month, etc. • Explore options Simon, Chapter 3

7. Filtering Data • Filtering data allows you to • Highlight particular characteristics in the data • Identify data elements based on comparisons to the rest of the data set • You can build customized filters that useful for you routine reports and analyzes. • Automates your work. Simon, Chapter 3

8. Filters: Filter.xls • Data => Filter => Indicated Options • Explore customized filter options Simon, Chapter 3

9. Scenarios Analysis • Excel Scenario Manager helps you • Set up scenarios • Explore alternative assumptions • Save “what if” results to compare effects • Note: Scenarios change your data. • Back up your original data set • Tools => Scenario Simon, Chapter 3

10. Scenarios: Scenario.xls • Save “what if” results to compare effects • Tools Scenario Add Simon, Chapter 3

11. Scenarios: Scenario.xls • Name Scenario • Identify target cells (B2 & B4) • OK • Now change values in B2 & B4 for scenario • Save Simon, Chapter 3

12. Validation: Validation.xls • Prevent data entry errors • Data Validate Specify rule Simon, Chapter 3

13. Entering Formulas: Autosum.xls • Have cursor in the cell that you want the formula to appear • Variety of ways to enter formula • Manually key them in (begin with =) • Buttons on tool bar • Drop down menu • Insert => Function • Editing Formulas • Copy and Paste Formulas Simon, Chapter 4

14. Solver: ChangeValues.xls • Use to target results – “reverse solution” • Provide solution and solve for parameters that produce the solution • Tools  Solver  Set Target (B4)Value = -1550 By Changing (“Guess”) B2..B3  Solve Simon, Chapter 4

15. Solver Specify Value for Solution: -1550 Set Target B4 Select Variables B2..B3 Simon, Chapter 4

16. IF Statements: ConditionalFormula.xls • Cell takes a value determined by a condition • IF(logical_test,value_if_true,value_if_false) Simon, Chapter 4

17. Additional Information in Chapter 4 • Including ranges of data in formulas • Tracing formulas • This is useful with complex spreadsheets • Looking up values • Ranking • Amortization tables • NOTE: Chapter 4 is chalked full of very useful information. Simon, Chapter 4