# XP XP by pqp12144

VIEWS: 62 PAGES: 10

• pg 1
```									                                                                                     XP                                                                           XP
Chapter Introduction
Chapter 2
•   Using data analysis tools to assist in problem solving
Solving Problems with Statistical                                                             Statistical functions that determine values
Functions that structure and analyze data
Analysis Tools                                                                       Functions that count and total data that meets
specified criteria
•   Performing a “what-if” analysis to examine effects of
“Averages don’t always reveal the most telling realities. You know                        changing specific worksheet values
Shaquille O’Neil and I have an average height of 6 feet.”
- U.S. Labor Secretary Robert Reich             •   Using Goal Seek to work backward to determine
(Mr. Reich is 4’10” tall, whereas the basketball star is 7’1” tall.)            input required to ensure a specific outcome

Chapter 2                   Succeeding in Business with Microsoft Office             1    Chapter 2                Succeeding in Business with Microsoft Office   2
Excel 2003: A Problem-Solving Approach                                                 Excel 2003: A Problem-Solving Approach

XP                                                                           XP
Chapter Introduction (continued)                                                         Functions Covered in this Chapter

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

Chapter 2                   Succeeding in Business with Microsoft Office             3    Chapter 2                Succeeding in Business with Microsoft Office   4
Excel 2003: A Problem-Solving Approach                                                 Excel 2003: A Problem-Solving Approach

Level 1 Objectives:      XP                                                                                                             XP
Using Statistical Functions to                                                                        Level I SAM Skills
Compare Data Values
•    Understand basic concepts related to statistics                                             •     Freeze rows and columns
•     Split a window into panes
•    Specify the precision of values
•     Use absolute references
•    Copy and paste information in a worksheet using                                             •     Use the AVERAGE function
Paste Special options                                                                       •     Use relative references
•    Calculate basic statistics: arithmetic mean, mode,                                          •     Use the ROUND function
median, standard deviation
•    Manage large worksheets by freezing panes and
splitting the window

Chapter 2                   Succeeding in Business with Microsoft Office             5    Chapter 2                Succeeding in Business with Microsoft Office   6
Excel 2003: A Problem-Solving Approach                                                 Excel 2003: A Problem-Solving Approach

1
XP                                                                                    XP
Understanding Fundamentals of                                                         Understanding Fundamentals of
Statistics                                                                            Statistics

Mean           Arithmetic average of a set of numbers

Median         Value in the middle
Mode           Occurs most frequently

Standard       How closely together the values are
deviation      distributed
Normal         Mean, median, and mode are the same
distribution   value

Chapter 2        Succeeding in Business with Microsoft Office                   7    Chapter 2          Succeeding in Business with Microsoft Office                  8
Excel 2003: A Problem-Solving Approach        Level 1 home                             Excel 2003: A Problem-Solving Approach        Level 1 home

XP                                                                                       XP
Understanding Fundamentals of                                                        Controlling the Precision of Data
Statistics                                                                   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

Chapter 2        Succeeding in Business with Microsoft Office                   9    Chapter 2          Succeeding in Business with Microsoft Office                  10
Excel 2003: A Problem-Solving Approach        Level 1 home                             Excel 2003: A Problem-Solving Approach        Level 1 home

XP                                                                                     XP
The ROUND Function                                                           Controlling the Precision of Data

Chapter 2        Succeeding in Business with Microsoft Office                  11    Chapter 2          Succeeding in Business with Microsoft Office                  12
Excel 2003: A Problem-Solving Approach        Level 1 home                             Excel 2003: A Problem-Solving Approach        Level 1 home

2
XP                                                                                           XP
Using Paste Special
Using Paste Special
to Copy and Paste Data

•   Simplest method: Copy button and Paste button on
Standard toolbar

Chapter 2         Succeeding in Business with Microsoft Office                  13    Chapter 2                Succeeding in Business with Microsoft Office                  14
Excel 2003: A Problem-Solving Approach        Level 1 home                                   Excel 2003: A Problem-Solving Approach        Level 1 home

XP                                                                                           XP
Calculating the MEAN, MEDIAN, MODE,
Using Paste Special                                                                and Standard Deviation (STDEV)

•   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

Chapter 2         Succeeding in Business with Microsoft Office                  15    Chapter 2                Succeeding in Business with Microsoft Office                  16
Excel 2003: A Problem-Solving Approach        Level 1 home                                   Excel 2003: A Problem-Solving Approach        Level 1 home

XP                                                                                           XP
Statistical Functions                                                                        Statistical Functions

Chapter 2         Succeeding in Business with Microsoft Office                  17    Chapter 2                Succeeding in Business with Microsoft Office                  18
Excel 2003: A Problem-Solving Approach        Level 1 home                                   Excel 2003: A Problem-Solving Approach        Level 1 home

3
Managing Large Worksheets by XP                                                                                                                        XP
Freezing Panes                                                                               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

Chapter 2                 Succeeding in Business with Microsoft Office                  19    Chapter 2        Succeeding in Business with Microsoft Office                  20
Excel 2003: A Problem-Solving Approach        Level 1 home                           Excel 2003: A Problem-Solving Approach        Level 1 home

Comparing Current Values toXP                                                                                                                           XP
Comparison Worksheet
Historical Values

•   Use a Comparison worksheet
Places current values and historical values on a
separate worksheet in the workbook, side by side

Chapter 2                 Succeeding in Business with Microsoft Office                  21    Chapter 2        Succeeding in Business with Microsoft Office                  22
Excel 2003: A Problem-Solving Approach        Level 1 home                           Excel 2003: A Problem-Solving Approach        Level 1 home

XP                                               XP
Calculating the Difference                                                        Calculating the Percent Difference
Between Two Sets of Data                                                              Between Two Sets of Data

Chapter 2                 Succeeding in Business with Microsoft Office                  23    Chapter 2        Succeeding in Business with Microsoft Office                  24
Excel 2003: A Problem-Solving Approach        Level 1 home                           Excel 2003: A Problem-Solving Approach        Level 1 home

4
XP                              Level 2 Objectives:                                           XP
Level 1 Summary                                                                    Organizing and Evaluating
Different Data Groupings
•   Application of problem-solving skills to a variety of                                     •   Evaluate the rank of each value in a data set
data sets, analyzing their statistical values (mean,                                      •   Determine the highest and lowest values in a data set
mode, median, and standard deviation)                                                     •   Determine the number of items that meet specified
•   Using these statistics, comparison of different data                                          criteria
sets to assess absolute as well as percentage                                             •   Determine a total value for items that meet specified
changes                                                                                       criteria
•   Using ROUND function to modify precision of values                                        •   Include relational operators and wildcards in formulas
in data sets                                                                                  to specify criteria

Chapter 2                 Succeeding in Business with Microsoft Office                  25    Chapter 2                 Succeeding in Business with Microsoft Office                  26
Excel 2003: A Problem-Solving Approach                                                        Excel 2003: A Problem-Solving Approach

XP                                                  XP
Determining a Rank for Each Value
Level 2 SAM Skills
in a Data Set

•   Create formulas using the COUNTIF function                                    •   RANK function
•   Create formulas using the SUMIF function                                                  Allows a list to be sorted, then counts number of
•   Use absolute references                                                                   entries either above or below the value in question
•   Use relative references                                                       • 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

Chapter 2                 Succeeding in Business with Microsoft Office                  27    Chapter 2                 Succeeding in Business with Microsoft Office                  28
Excel 2003: A Problem-Solving Approach                                                        Excel 2003: A Problem-Solving Approach        Level 2 home

XP
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)

Chapter 2                 Succeeding in Business with Microsoft Office                  29    Chapter 2                 Succeeding in Business with Microsoft Office                  30
Excel 2003: A Problem-Solving Approach        Level 2 home                                    Excel 2003: A Problem-Solving Approach        Level 2 home

5
XP                                                    XP
Determining the Number of Items
Determining Values
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

Chapter 2        Succeeding in Business with Microsoft Office                  31    Chapter 2              Succeeding in Business with Microsoft Office                  32
Excel 2003: A Problem-Solving Approach        Level 2 home                                 Excel 2003: A Problem-Solving Approach        Level 2 home

XP                                                                                                                            XP
Types of Criteria Specified for the
Relational Operators
COUNTIF Function

Chapter 2        Succeeding in Business with Microsoft Office                  33    Chapter 2              Succeeding in Business with Microsoft Office                  34
Excel 2003: A Problem-Solving Approach        Level 2 home                                 Excel 2003: A Problem-Solving Approach        Level 2 home

XP                                                                                         XP
Sample Result of COUNTIF
Wildcards
Function

Chapter 2        Succeeding in Business with Microsoft Office                  35    Chapter 2              Succeeding in Business with Microsoft Office                  36
Excel 2003: A Problem-Solving Approach        Level 2 home                                 Excel 2003: A Problem-Solving Approach        Level 2 home

6
Determining a Total Value forXP                                                                                                                            XP
Examples of SUMIF Function
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

Chapter 2                 Succeeding in Business with Microsoft Office                  37    Chapter 2          Succeeding in Business with Microsoft Office                  38
Excel 2003: A Problem-Solving Approach        Level 2 home                             Excel 2003: A Problem-Solving Approach        Level 2 home

XP                       Level 3 Objectives:     XP
Level 2 Summary                                                          Extending the Analysis with
What-If, Goal Seek, and Simulation
•   Tail values – highest and lowest five elements of                                         •   Perform what-if analyses
each data set – and their relative rankings                                               •   Perform reverse what-if analyses using the Goal
•   Summarizing analyses by counting the number of                                                Seek tool
items that meet specific criteria and summing items                                       •   Analyze data by category by combining functions
that meet specific criteria                                                               •   Simulate data to evaluate different outcomes
•   Including relational operators and wildcards in
functions and formulas

Chapter 2                 Succeeding in Business with Microsoft Office                  39    Chapter 2          Succeeding in Business with Microsoft Office                  40
Excel 2003: A Problem-Solving Approach                                                 Excel 2003: A Problem-Solving Approach

XP                                                                                     XP
Level 3 SAM Skills                                                           Evaluating a Larger Data Set

•   Create a custom number format                                                 •   Separately list inputs, especially those likely to
•   Create formulas using the COUNTIF function                                        change or that you might want to explore with “what-
•   Create formulas using the SUMIF function                                          if” scenarios
•   Use Goal Seek                                                                 •   If a large number of data inputs, place them on a
•   Use the ROUND function                                                            separate worksheet or in a separate area on the
same worksheet

Chapter 2                 Succeeding in Business with Microsoft Office                  41    Chapter 2          Succeeding in Business with Microsoft Office                  42
Excel 2003: A Problem-Solving Approach                                                 Excel 2003: A Problem-Solving Approach        Level 3 home

7
Specifying a Custom NumberXP                                                                                                                                    XP

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

Chapter 2                Succeeding in Business with Microsoft Office                  43    Chapter 2                Succeeding in Business with Microsoft Office                  44
Excel 2003: A Problem-Solving Approach        Level 3 home                                   Excel 2003: A Problem-Solving Approach        Level 3 home

XP
Considering Alternatives: XP
Custom Number Formatting Codes
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

Chapter 2                Succeeding in Business with Microsoft Office                  45    Chapter 2                Succeeding in Business with Microsoft Office                  46
Excel 2003: A Problem-Solving Approach        Level 3 home                                   Excel 2003: A Problem-Solving Approach        Level 3 home

XP                                                                                           XP
Using Goal Seek                                                                              Using Goal Seek

Chapter 2                Succeeding in Business with Microsoft Office                  47    Chapter 2                Succeeding in Business with Microsoft Office                  48
Excel 2003: A Problem-Solving Approach        Level 3 home                                   Excel 2003: A Problem-Solving Approach        Level 3 home

8
XP                                                                                      XP
Using Goal Seek                                                                         Using Goal Seek

Chapter 2          Succeeding in Business with Microsoft Office                  49    Chapter 2           Succeeding in Business with Microsoft Office                  50
Excel 2003: A Problem-Solving Approach        Level 3 home                              Excel 2003: A Problem-Solving Approach        Level 3 home

XP                                                                                      XP
Combining COUNTIF and SUMIF to
Limitations of Goal Seek                                                          Analyze Data in Specific Categories

•   Allows you to vary only a single output                                            •   Necessary to overcome the drawback that no single
•   Input must be a constant value, not a value derived                                    function in Excel can average a series of values if
from a formula                                                                         they meet specific criteria

Chapter 2          Succeeding in Business with Microsoft Office                  51    Chapter 2           Succeeding in Business with Microsoft Office                  52
Excel 2003: A Problem-Solving Approach        Level 3 home                              Excel 2003: A Problem-Solving Approach        Level 3 home

XP                                                                                      XP
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)

Chapter 2          Succeeding in Business with Microsoft Office                  53    Chapter 2           Succeeding in Business with Microsoft Office                  54
Excel 2003: A Problem-Solving Approach        Level 3 home                              Excel 2003: A Problem-Solving Approach        Level 3 home

9
XP                                                                                           XP
Simulation Worksheet                                                                   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
•   Use RAND function to assign a random value
Has no arguments, but must include parentheses
•   Use ROUND function to calculate probable costs

Chapter 2          Succeeding in Business with Microsoft Office                  55    Chapter 2                Succeeding in Business with Microsoft Office                  56
Excel 2003: A Problem-Solving Approach        Level 3 home                                   Excel 2003: A Problem-Solving Approach        Level 3 home

XP                                                                                           XP
Automation Calculation                                                                                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

Chapter 2          Succeeding in Business with Microsoft Office                  57    Chapter 2                Succeeding in Business with Microsoft Office                  58
Excel 2003: A Problem-Solving Approach        Level 3 home                                   Excel 2003: A Problem-Solving Approach

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

Chapter 2          Succeeding in Business with Microsoft Office                  59
Excel 2003: A Problem-Solving Approach

10

```
To top