week 3 excel

Anyelis Diaz

Chapter02-Lecture.pdf

Home >Business & Finance homework help >Accounting homework help >week 3 excel

Solving Problems with Statistical Analysis Tools

Chapter 2

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 the effects of changing specific worksheet values

• Using Goal Seek to work backward to determine the input required to ensure a specific outcome

Succeeding in Business with Microsoft Excel 2013: Chapter 2 2

Chapter Introduction (continued)

• Simulating possible outcomes with a set of inputs

• Using custom formatting techniques

To go to Level 1, click here

To go to Level 2, click here

To go to Level 3, click here

Succeeding in Business with Microsoft Excel 2013: Chapter 2 3

Functions Covered in This Chapter

• AVERAGE

• AVERAGEIF

• COUNTIF

• LARGE

• MEDIAN

• MODE.SNGL

• RAND

• RANDBETWEEN

• RANK.EQ

• ROUND

• SMALL

• STDEV.S

• SUMIF

Succeeding in Business with Microsoft Excel 2013: Chapter 2 4

Level 1 Objectives: Using Statistical Functions to Compare Data Values

• Understand basic concepts related to statistics

• Specify the precision of values using the ROUND function

• 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

Succeeding in Business with Microsoft Excel 2013: Chapter 2 5

Understanding the Fundamentals of Statistics

Mean Arithmetic average of a set of numbers

Median Value in the middle

Mode Occurs most frequently

Standard

deviation

How closely together the values are

dispersed from the arithmetic mean

Normal

distribution

Mean, median, and mode are the same

value

Succeeding in Business with Microsoft Excel 2013: Chapter 2 6Level 1 home

Understanding the Fundamentals of Statistics (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 7Level 1 home

Understanding the Fundamentals of Statistics (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 8Level 1 home

Controlling the Precision of Data Using the ROUND Function

• Round function

– Changes the 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

• “Set precision as displayed” option

Succeeding in Business with Microsoft Excel 2013: Chapter 2 9Level 1 home

Controlling the Precision of Data Using the ROUND Function (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 10Level 1 home

Controlling the Precision of Data Using the ROUND Function (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 11Level 1 home

Controlling the Precision of Data Using the ROUND Function (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 12Level 1 home

Using Paste Special to Copy and Paste Data

• Simplest method: Copy button and Paste button in the Clipboard group on the HOME tab

Succeeding in Business with Microsoft Excel 2013: Chapter 2 13Level 1 home

Using Paste Special to Copy and Paste Data (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 14Level 1 home

Using Paste Special to Copy and Paste Data (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 15

Calculating the Mean, Median, Mode, and Standard Deviation

• 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

Succeeding in Business with Microsoft Excel 2013: Chapter 2 16Level 1 home

Calculating the Mean, Median, Mode, and Standard Deviation (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 17Level 1 home

Calculating the Mean, Median, Mode, and Standard Deviation (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 18Level 1 home

Managing Large Worksheets by Freezing Panes and Splitting the Window

• Freezing panes

– Keeps titles displayed in the top pane; allows the bottom pane to be scrolled

– Both titles and values farther down are displayed simultaneously

• Splitting the window

– Allows you to scroll each portion individually, top/bottom and left/right, with separate scroll bars

Succeeding in Business with Microsoft Excel 2013: Chapter 2 19Level 1 home

Managing Large Worksheets by Freezing Panes and Splitting the Window (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 20Level 1 home

Comparing Current Values with Historical Values

• Use a Comparison worksheet

– Places current values and historical values on a separate worksheet in the workbook, side by side

Succeeding in Business with Microsoft Excel 2013: Chapter 2 21Level 1 home

Comparing Current Values with Historical Values (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 22Level 1 home

Comparing Current Values with Historical Values (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 23Level 1 home

Comparing Current Values with Historical Values (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 24Level 1 home

Level 1 Summary

• Applying problem-solving skills to a variety of data sets, and analyzing their statistical values (mean, mode, median, and standard deviation)

• Using these statistics to compare different data sets in order to assess absolute as well as percent changes

• Using the ROUND function to modify the precision of values in data sets

Succeeding in Business with Microsoft Excel 2013: Chapter 2 25

Level 2 Objectives: Organizing and Evaluating Different Data Groupings

• Use the Quick Analysis tool to aggregate data

• 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

Succeeding in Business with Microsoft Excel 2013: Chapter 2 26

Quick Insertion of Aggregate Values for a Data Set

• Quick Analysis

– A new contextual tool that enables easy access to data-analysis features such as formulas, tables, charts, conditional formatting, sparklines, and PivotTables

Succeeding in Business with Microsoft Excel 2013: Chapter 2 27Level 2 home

Quick Insertion of Aggregate Values for a Data Set (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 28Level 2 home

Quick Insertion of Aggregate Values for a Data Set (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 29Level 2 home

Determining a Rank for Each Value in a Data Set

• RANK.EQ function

– Allows a list to be sorted, then counts the number of entries either above or below the value in question

• RANK.EQ(number,ref,[order])

– If the order argument is 0 or left blank, the values are ranked in descending order

– If the order argument is a positive number, the values are ranked in ascending order

Succeeding in Business with Microsoft Excel 2013: Chapter 2 30Level 2 home

Succeeding in Business with Microsoft Excel 2013: Chapter 2 31Level 2 home

Determining the Highest and 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)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 32Level 2 home

Determining the Highest and Lowest Values in a Data Set (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 33Level 2 home

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

Succeeding in Business with Microsoft Excel 2013: Chapter 2 34Level 2 home

Determining the Number of Items That Meet Specified Criteria (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 35Level 2 home

Determining the Number of Items That Meet Specified Criteria (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 36Level 2 home

Determining the Number of Items That Meet Specified Criteria (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 37Level 2 home

Determining the Number of Items That Meet Specified Criteria (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 38Level 2 home

Determining a Total Value for Items That Meet Specified Criteria

• Use the SUMIF function to add all values in a range that meet specified criteria – =SUMIF(range,criteria,sum_range)

• sum_range argument identifies the corresponding cell range to sum if specified criteria have been met in the range established by the range argument

Succeeding in Business with Microsoft Excel 2013: Chapter 2 39Level 2 home

Determining a Total Value for Items That Meet Specified Criteria (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 40Level 2 home

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

Succeeding in Business with Microsoft Excel 2013: Chapter 2 41

Level 3 Objectives: Extending the Analysis with

What-If, Goal Seek, and Simulation

• Evaluate a large data set

• Apply custom number formats to data

• Perform what-if analyses

• Perform reverse what-if analyses using Goal Seek

• Analyze data by category by combining functions

• Simulate data to evaluate different outcomes

Succeeding in Business with Microsoft Excel 2013: Chapter 2 42

Evaluating a Larger Data Set

• Separately list inputs, especially those likely to change or that you might want to explore with “what-if” scenarios

• If there is a large number of data inputs, place them on a separate worksheet or in a separate area on the same worksheet

Succeeding in Business with Microsoft Excel 2013: Chapter 2 43Level 3 home

Specifying a Custom Number Format

• Use to alter the 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

Succeeding in Business with Microsoft Excel 2013: Chapter 2 44Level 3 home

Succeeding in Business with Microsoft Excel 2013: Chapter 2 45Level 3 home

Specifying a Custom Number Format (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 2 46Level 3 home

Considering Alternatives: 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

Succeeding in Business with Microsoft Excel 2013: Chapter 2 47Level 3 home