1 / 15

Chapter 8

Chapter 8. Data Analysis. Agenda. Functions AND and OR COUNT, COUNTA, and COUNTIF CONCATENATE and TRIM RANK and QUARTILE MOD and ROW Goal Seek in decision-making Pareto chart for the 80/20 rule Advanced features in pivot tables and pivot charts. Data Analysis Techniques.

dmerritt
Download Presentation

Chapter 8

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 8 Data Analysis

  2. Agenda • Functions • AND and OR • COUNT, COUNTA, and COUNTIF • CONCATENATE and TRIM • RANK and QUARTILE • MOD and ROW • Goal Seek in decision-making • Pareto chart for the 80/20 rule • Advanced features in pivot tables and pivot charts

  3. Data Analysis Techniques • Sorting – arrange lists by one or more fields • Calculating – manipulating data in an individual record • Summarizing – displays records within groups and calculating (pivot table) • Filtering – displays only records that meet specific criteria • Formatting – calls attention to a cell by the way it is displayed (conditional formatting) • Charting – graphic representation of data

  4. Summarizing Functions • COUNT – number of cells containing numeric values within a designated range • COUNTA – number of non-empty cells within a designated range • COUNTIF – number of cells meeting specific criteria with a designated range

  5. Decision-Making Functions • AND – True if all arguments are true • OR – True if any arguments are true • IF – a logical test • One value if true, another value if false • Nested If – using another If function as either the true or false value

  6. The Admissions Office • Examine a list of applicants and determine the acceptances and rejections • Illustrate the AND or OR functions • Use conditional formatting • Demonstrate AutoFilter and Goal Seek functions

  7. Ranking Functions • RANK – rank of a number in a list with two arguments • Number • Name of list • QUARTILE – maximum value of specified quartile with two arguments • Name of array • Quartile

  8. Modulo Arithmetic • MOD function returns remainder after division • Requires two arguments • Value • Divisor

  9. The Graduating Class • Use the QUARTILE and RANK functions to determine the position of records within a list • Use the SUBTOTALS function to compute statistics for a group of records • Use conditional formatting and MOD function to shade alternate rows within a worksheet • Demonstrate CONCATENATE and TRIM functions

  10. A Pivot Table and Chart • Divides records into list • Computes summary statistics for categories • Drag fields around pivot table to create new views • Pivot chart displays pivot table graphically

  11. The Men’s Store • Import data from a text file into an Excel workbook • Create a pivot table and associated pivot chart • Use the Group and Show Detail command • Create a worksheet from a pivot table

  12. The Pareto Principle • 80% of the activity in a system is attributable to 20% of the transactions (also called the “80/20 Rule”) • Pareto chart – displays percentage each data element contributes to the whole

  13. The Restaurant • Display the day of the week given a calendar date • Create a pivot table and corresponding pivot chart • Determine percentage values within a pivot table • Convert to pivot chart to a Pareto chart

  14. Points to Remember • Functions • AND and OR • COUNT, COUNTA, and COUNTIF • CONCATENATE and TRIM • RANK and QUARTILE • MOD and ROW • Goal Seek in decision-making • Pareto chart for the 80/20 rule • Advanced features in pivot tables and pivot charts

  15. Assignment • Practice exercises: 2, 3 and 4 • Due data:

More Related