Data Analysis

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