Docstoc

Solving Problems with Statistical Analysis Tools

Document Sample
Solving Problems with Statistical Analysis Tools Powered By Docstoc
					XP

Solving Problems with Statistical Analysis Tools
“Averages don’t always reveal the most telling realities. You know Shaquille O’Neil and I have an average height of 6 feet.” - U.S. Labor Secretary Robert Reich
(Mr. Reich is 4’10” tall, whereas the basketball star is 7’1” tall.)

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003

1

XP

Chapter Introduction
•
Using data analysis tools to assist in problem solving
 Statistical functions that determine values  Functions that structure and analyze data  Functions that count and total data that meets

• •

specified criteria

Performing a “what-if” analysis to examine effects of changing specific worksheet values Using Goal Seek to work backward to determine input required to ensure a specific outcome

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003

2

XP

Chapter Introduction (continued)
• • •
Simulating possible outcomes with a set of inputs Using custom formatting techniques Functions covered in this chapter: AVERAGE, COUNTIF, LARGE, MEDIAN, MODE, RAND, RANDBETWEEN, RANK, ROUND, SMALL, STDEV, SUMIF

To go to Level 1, click here To go to Level 2, click here To go to Level 3, click here
Section 2, Chapter 2
Succeeding in Business Applications with MS Office 2003

3

XP

Functions Covered in this Chapter
• • • • • • • • • • • •
AVERAGE COUNTIF LARGE MEDIAN MODE RAND RANDBETWEEN RANK ROUND SMALL STDEV SUMIF
Succeeding in Business Applications with MS Office 2003

Section 2, Chapter 2

4

Level 1 Objectives: XP Using Statistical Functions to Compare Data Values
• • •
Understand basic concepts related to statistics Specify the precision of values Copy and paste information in a worksheet using Paste Special options Calculate basic statistics: arithmetic mean, mode, median, standard deviation Manage large worksheets by freezing panes and splitting the window

• •

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003

5

XP

Level I SAM Skills
• • • • • •
Freeze rows and columns Split a window into panes Use absolute references Use the AVERAGE function Use relative references Use the ROUND function

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003

6

XP Understanding Fundamentals of Statistics

Mean
Median

Arithmetic average of a set of numbers
Value in the middle

Mode Standard deviation Normal distribution
Section 2, Chapter 2

Occurs most frequently How closely together the values are distributed Mean, median, and mode are the same value
Succeeding in Business Applications with MS Office 2003 Level 1 home

7

XP Understanding Fundamentals of Statistics

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 1 home

8

XP Understanding Fundamentals of Statistics

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 1 home

9

XP Controlling the Precision of Data Using the ROUND Function

• Changes precision of data values stored • =ROUND (number,num_digits) • Two different types of arguments • Importance of adhering to function syntax when

•

working with multiple arguments “Precision as displayed” option

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 1 home

10

XP

The ROUND Function

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 1 home

11

XP

Controlling the Precision of Data

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 1 home

12

Using Paste Special to Copy and Paste Data
•
Simplest method: Copy button and Paste button on Standard toolbar

XP

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 1 home

13

XP

Using Paste Special

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 1 home

14

XP

Using Paste Special

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 1 home

15

Calculating the MEAN, MEDIAN, MODE, and Standard Deviation (STDEV)

XP

•

Contain only one type of argument – a list of values
 Constants  Cell references  Range of cells along a column  Range of cells along a row  Two-dimensional block of cells

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 1 home

16

XP

Statistical Functions

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 1 home

17

XP

Statistical Functions

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 1 home

18

Managing Large Worksheets by XP Freezing Panes and Splitting the Window

•

Freezing panes
 Keeps titles displayed in top pane; allows bottom pane

to be scrolled  Both titles and values further down are displayed simultaneously

•

Splitting the window
 Allows you to scroll each portion individually,

top/bottom and left/right, with separate scroll bars

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 1 home

19

XP

Freezing Panes

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 1 home

20

Comparing Current Values toXP Historical Values
•
Use a Comparison worksheet
 Places current values and historical values on a

separate worksheet in the workbook, side by side

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 1 home

21

XP

Comparison Worksheet

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 1 home

22

Calculating the Difference Between Two Sets of Data

XP

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 1 home

23

XP Calculating the Percent Difference Between Two Sets of Data

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 1 home

24

XP

Level 1 Summary
•
Application of problem-solving skills to a variety of data sets, analyzing their statistical values (mean, mode, median, and standard deviation) Using these statistics, comparison of different data sets to assess absolute as well as percentage changes Using ROUND function to modify precision of values in data sets

• •

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003

25

Level 2 Objectives: Organizing and Evaluating Different Data Groupings
• • •

XP

• •

Evaluate the rank of each value in a data set Determine the highest and lowest values in a data set Determine the number of items that meet specified criteria Determine a total value for items that meet specified criteria Include relational operators and wildcards in formulas to specify criteria

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003

26

XP

Level 2 SAM Skills
• • • •
Create formulas using the COUNTIF function Create formulas using the SUMIF function Use absolute references Use relative references

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003

27

XP Determining a Rank for Each Value in a Data Set

•

RANK function
 Allows a list to be sorted, then counts number of

entries either above or below the value in question

• RANK

(number,ref,order)

 If “order” argument is 0 or left blank, values are ranked

in descending order  If “order” argument is a positive number, values are ranked in ascending order

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 2 home

28

XP

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 2 home

29

Determining the Highest and XP Lowest Values in a Data Set
•
LARGE function
 Determines the nth largest value in a range  LARGE (array,k)

•

SMALL function
 Determines the nth smallest value in a range  SMALL (array,k)

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 2 home

30

XP

Determining Values

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 2 home

31

XP Determining the Number of Items that Meet Specified Criteria

•

COUNTIF function
 Counts the number of items in a range that

meet specified criteria  =COUNTIF (range,criteria)
 Range argument must be a contiguous set of

cells  Criteria argument is a “test” that data must meet in order to be counted in the grouping
Section 2, Chapter 2
Succeeding in Business Applications with MS Office 2003 Level 2 home

32

XP Types of Criteria Specified for the COUNTIF Function

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 2 home

33

XP

Relational Operators

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 2 home

34

XP

Wildcards

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 2 home

35

Sample Result of COUNTIF Function

XP

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 2 home

36

Determining a Total Value forXP Items that Meet Specified Criteria
•
Use SUMIF function to add all values in a range that meet specified criteria  =SUMIF (range,criteria,sum_range)
 Sum_range argument identifies corresponding

cell range to sum if specified criteria have been met in the range established by the “range” argument

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 2 home

37

XP

Examples of SUMIF Function

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 2 home

38

XP

Level 2 Summary
•
Tail values – highest and lowest five elements of each data set – and their relative rankings Summarizing analyses by counting the number of items that meet specific criteria and summing items that meet specific criteria Including relational operators and wildcards in functions and formulas

• •

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003

39

Level 3 Objectives: XP Extending the Analysis with What-If, Goal Seek, and Simulation
• •
Perform what-if analyses Perform reverse what-if analyses using the Goal Seek tool Analyze data by category by combining functions Simulate data to evaluate different outcomes

• •

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003

40

XP

Level 3 SAM Skills
• • • • •
Create a custom number format Create formulas using the COUNTIF function Create formulas using the SUMIF function Use Goal Seek Use the ROUND function

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003

41

XP

Evaluating a Larger Data Set
•
Separately list inputs, especially those likely to change or that you might want to explore with “whatif” scenarios If a large number of data inputs, place them on a separate worksheet or in a separate area on the same worksheet

•

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 3 home

42

Specifying a Custom NumberXP Format
• •
Use to alter format of a cell without altering the value Format codes
 Can include up to four parts, each separated by a

semicolon: one for positive numbers, one for negative numbers, one for zero values, one for text  Consist of combinations of symbols, each with a separate meaning

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 3 home

43

XP

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 3 home

44

XP

Custom Number Formatting Codes

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 3 home

45

Considering Alternatives: XP What-if Analysis and Goal Seek
•
What-if analysis  Determines the outcome of changing one or more input values and evaluates the recalculated results Goal Seek tool
 Specifies the outcome you want and which input value

•

you want to vary  Excel automatically calculates the solution

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 3 home

46

XP

Using Goal Seek

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 3 home

47

XP

Using Goal Seek

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 3 home

48

XP

Using Goal Seek

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 3 home

49

XP

Using Goal Seek

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 3 home

50

XP

Limitations of Goal Seek
• •
Allows you to vary only a single output Input must be a constant value, not a value derived from a formula

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 3 home

51

Combining COUNTIF and SUMIF to Analyze Data in Specific Categories

XP

•

Necessary to overcome the drawback that no single function in Excel can average a series of values if they meet specific criteria

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 3 home

52

XP

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 3 home

53

Analyzing Data Through Simulation
•
Analytical method that creates artificially generated data to imitate real data Easily calculated and recalculated to show different possible outcomes (as opposed to most probable outcome or even extreme limits)

XP

•

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 3 home

54

XP

Simulation Worksheet

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 3 home

55

XP

Functions Used in Simulation
•
Use RANDBETWEEN function to randomly assign a number between two values
 RANDBETWEEN (bottom,top)  Not automatically provided with Excel by default; add

using Add-In Analysis ToolPak

• •

Use RAND function to assign a random value
 Has no arguments, but must include parentheses

Use ROUND function to calculate probable costs

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 3 home

56

XP

Automation Calculation

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003 Level 3 home

57

XP

Level 3 Summary
•
Performing a cost benefit analysis using a larger data set
 Specifying a custom number format  Performing what-if analysis  Using the Goal Seek tool
 Combining COUNTIF and SUMIF functions to analyze

data in categories  Analyzing data through simulation using RAND and RANDBETWEEN functions

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003

58

XP

Chapter Summary
• • •
Using statistical functions to compare data values Organizing and evaluating different data groupings Extending the analysis with what-if, Goal Seek, and simulation

Section 2, Chapter 2

Succeeding in Business Applications with MS Office 2003

59


				
DOCUMENT INFO
Shared By:
Tags: Statistic
Stats:
views:1212
posted:2/6/2008
language:English
pages:59
user002 user002
About