Lab 2 data evaluation validation excel scenarios functions simon chapter 2 3
Download
1 / 17

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


  • 287 Views
  • Uploaded on

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

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

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 2 data evaluation validation excel scenarios functions simon chapter 2 3 l.jpg

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 l.jpg
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 l.jpg
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


Conditional formatting conditionalformat xls l.jpg
Conditional Formatting(ConditionalFormat.xls)

  • Format => Conditional Formatting

Simon, Chapter 3


Subtotals l.jpg
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 l.jpg
Subtotals: Subtotal.xls

  • Select entire data block

  • Data => Subtotal=>By month, etc.

  • Explore options

Simon, Chapter 3


Filtering data l.jpg
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 l.jpg
Filters: Filter.xls

  • Data => Filter => Indicated Options

  • Explore customized filter options

Simon, Chapter 3


Scenarios analysis l.jpg
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 l.jpg
Scenarios: Scenario.xls

  • Save “what if” results to compare effects

  • Tools Scenario Add

Simon, Chapter 3


Scenarios scenario xls11 l.jpg
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 l.jpg
Validation: Validation.xls

  • Prevent data entry errors

  • Data Validate Specify rule

Simon, Chapter 3


Entering formulas autosum xls l.jpg
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 l.jpg
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


Solver l.jpg
Solver

Specify Value for Solution: -1550

Set Target

B4

Select

Variables B2..B3

Simon, Chapter 4


If statements conditionalformula xls l.jpg
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 l.jpg
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


ad