Loading in 5 sec....

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

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

- 288 Views
- Uploaded on

Download Presentation
## PowerPoint Slideshow about '' - chandler

**An Image/Link below is provided (as is) to download presentation**

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

### 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

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

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

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

Subtotals: Subtotal.xls

- Select entire data block
- Data => Subtotal=>By month, etc.
- Explore options

Simon, Chapter 3

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

Filters: Filter.xls

- Data => Filter => Indicated Options
- Explore customized filter options

Simon, Chapter 3

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

Scenarios: Scenario.xls

- Save “what if” results to compare effects
- Tools Scenario Add

Simon, Chapter 3

Scenarios: Scenario.xls

- Name Scenario
- Identify target cells (B2 & B4)
- OK
- Now change values in B2 & B4 for scenario
- Save

Simon, Chapter 3

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

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

IF Statements: ConditionalFormula.xls

- Cell takes a value determined by a condition
- IF(logical_test,value_if_true,value_if_false)

Simon, Chapter 4

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

Download Presentation

Connecting to Server..