XP XP by pqp12144

VIEWS: 62 PAGES: 10

									                                                                                     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
To go to Level 1, click here                                                              •   ROUND
To go to Level 2, click here                                                              •   SMALL
To go to Level 3, click here                                                              •   STDEV
                                                                                          •   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
                                                                                                   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



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