1 / 17

# Lab 2 Data Evaluation Validation Excel Scenarios Functions Simon Chapter 2 3 - PowerPoint PPT Presentation

Lab 2 Data Evaluation & Validation Excel Scenarios & Functions Simon 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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Lab 2 Data Evaluation Validation Excel Scenarios Functions Simon Chapter 2 3' - 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

• Chapter 3

• ConditionalFormat.xls

• Subtotal.xls

• Filter.xls

• Scenario.xls

• Validation.xls

• Chapter 4

• Autosum.xls

• ChangeValues.xls

• ConditionalFormula.xls

• 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

Conditional Formatting(ConditionalFormat.xls)

• Format => Conditional Formatting

Simon, Chapter 3

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

• 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

Validation: Validation.xls

• Prevent data entry errors

• Data Validate Specify rule

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

Specify Value for Solution: -1550

Set Target

B4

Select

Variables B2..B3

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

• 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