# Descriptive Statistics Lab

Document Sample

```					           Descriptive Statistics Lab
Part 1: Table Operations
FORMAT A BLOCK OF TEXT AS A TABLE

Data is often organized as a table.
In Excel, you can designate a range of cells as a table.
If you do so, Excel can do smart things with it.

1. SELECT “FORMAT AS TABLE”

1. Select the entire table
by clicking in one corner
and dragging to another
corner.
If there are no
blank cells, you
can click any cell
in the table
2. Select the Home tab.
3. Click on Format as
Table in the Styles
group.
Descriptive Statistics Lab

2. CHOOSE A STYLE

After you click on Format as Table, you
select a style for your table.
Click on a style that looks like it suits
Don’t worry. You can change it later.

You will be asked to confirm that the selected
If this is not correct, change it by
entering the correct range in the
blackened input area.
(label at the top of each column).
If there are headers, click the check box “My

YOUR TABLE IS NOW DEFINED AND FORMATTED

Hands On: Do this step yourself.

Page 2
Descriptive Statistics Lab

4. REFINING THE DESIGN

1. Click
anywhere in
the table.
2, A new tab
appears
under “Table
Tools.” This
is Design.
Click on it.
3. Give your
table a
descriptive
name.

4, Go to the   Your table has a header row and banded columns. This is correct for your data.
Table Style
Sometimes, the first column is also a header.
Options
group.         We will look at the total row later.

Hands On: Do this step yourself.

Page 3
Descriptive Statistics Lab

SORTING THE ROWS

You often want to sort the rows to see patterns in the table.
The small drop-down arrows in the column headers do this.

THE TABLE BEFORE SORTING

Unsorted location data.
Drop-down arrow. You
will click on the drop-
down arrow to sort the
column.

CLICK ON THE LOCATION DROP-DOWN BOX TO SORT BY LOCATION ALPHABETICALLY

1. Click on the Drop-Down Arrow.
2. Select Sort A to Z to sort alphabetically.

THE RESULT

The table is sorted alphabetically by location.

Hands On: 1. Sort by age, oldest to youngest.
2. Sort by income, in ascending order.
3. Sort by location, alphabetically.

Page 4
Descriptive Statistics Lab

COUNTING FREQUENCIES IN SORTED DATA

You wish to create a frequency distribution by location.

1. Highlight the
range of the first
city—Antwerp.
2. The box in the
upper left shows
that you have
highlighted 3 rows
and 1 column.
This means that the
count for
employees working
in Antwerp is 3.
You can count the
frequency of
employees in Berlin
the same way.
Then go on to
Chicago.

Now you can create    City             Employees
the summary table
(frequency            Antwerp                    3
distribution.)
Berlin

Chicago

Lahore

Total

Hands On: Complete the frequency distribution.

Page 5
Descriptive Statistics Lab

TURN THE DROP-DOWN ARROWS ON AND OFF

1. Click anywhere in the table.
2. Go to the Data tab.
3. Clicking on the large filter icon toggles all of
the drop-down arrows on and off.

Hands On: Try it.

Page 6
Descriptive Statistics Lab

COMPUTING STATISTICS WITH A TOTAL ROW

1. Click on any cell in the table.
2. Under Table Tools, select Design.
Caution: If Table Tools and Design do
not appear, you have not first clicked on
a cell in the table.
3. Under Table Style Options, click on Total
Row

A total row appears.
For the last column, Excel automatically adds a
sum.
If you click on a cell in the total row, you can
decide what statistic you wish to appear there.

Hands On: Create 1) a sum for income, 2) a count for location, and 3) a median for years with
the firm.

Page 7
Descriptive Statistics Lab

SORTING ON TWO COLUMNS

You wish to sort by first and last name.

You must sort by Last and First, but in what order
do you do it?
The answer: Sort by the main category LAST.
So sort by first name first because it is not the main
category.
Then sort by last name.
Note that people with the last name Jones are
sorted properly.
If you sorted by last, and then first, your data would
be sorted by first name.

Hands On: Try it.

COUNTING DATA FOR A CROSS-TAB

Sort by Gender, and then by Location.
Highlight the number of female employees in
Lahore. You see that this gives you three rows. So
three female employees work in Lahore.
You can now begin filling out the cross tab.

Female               Male

Antwerp                         0                 3

Berlin

Chicago

Lahore                          3

Total

Hands On: Complete the frequency distribution.

Page 8
Descriptive Statistics Lab

Part 2: Filtering
PERSPECTIVE

Sorting sorts the rows and leaves all rows visible.
Filtering only shows some rows, for easier visualization.
Filtering does not delete the unseen rows.

THE DOWN ARROW OPTIONS

COMPONENTS

Page 9
Descriptive Statistics Lab

SELECTING ANTWERP ONLY

By default, all choices are selected.
1. Click on (Select All) to unselect everything.
2. Click on Antwerp only to select it.

THE RESULTS

Hands On: Try filtering to show only employees in Antwerp.

CLEARING FILTERS

You must clear filters one column at a time.
1. If a column has a funnel icon, it is filtered.
2. Click on the filter icon
3. Select Clear Filter From “nameofcolumn”

Hands On: 1) Clear the filter. 2) Do a new filter showing employees in Chicago AND Berlin.

Page 10
Descriptive Statistics Lab

TEXT FILTER OPTIONS

Hands On: Filter for employees whose Last Name begins with “T”.

Page 11
Descriptive Statistics Lab

NUMBER FILTER OPTIONS (FOR NUMERICAL COLUMNS)

TOP “10”

Hands On: Filter on salaries above the average. Clear the filter.
Filter on age over 50. Clear the filter.
Filter on the top three salaries. Clear the filter.
Filter on ages between 50 and 60. Clear the filter.

Page 12
Descriptive Statistics Lab

Part 3: Conditional Formatting
PERSPECTIVE

Sorting sorts rows, showing all rows.
Filtering shows only selected rows.
Conditional formatting only changes the format of cells, for easier visualization

BEGINNING CONDITIONAL FORMATTING

1. Select cells to
be conditionally
formatted.
Simply
click on
the filter
arrow to
select
them
2. On the Home
tab,
3. Click on
Conditional
Formatting
Then select a
type of
conditional
formatting.
We will only
look at data bars.

Page 13
Descriptive Statistics Lab

DATA BARS

Select the data
Go to the Home tab,
Click on Conditional Formatting.
Select Data Bars.
Choose a color.

WHAT YOU GET

The width of the bars is proportional the value in
the cell.

Hands On: Add data bars to Age as shown.
Add data bars to Years with the Firm.

Page 14
Descriptive Statistics Lab

“TOP 10” CONDITIONAL FORMATTING

Top/Bottom rules take you the Top
and Bottom “10” rules for
elements or percent.
They also take you to above and
below average.

EXAMPLE: TOP 2

Choose “Top 10” items.
In the Top 10 Items dialog box, choose the top 3
items.
Select a dell format.

Hands On: Format the oldest four employees. Remove the formatting.
Format everyone below the average age.

Page 15
Descriptive Statistics Lab

Part 4: PivotTables
PERSPECTIVE

Doing sorting and then counts is time-consuming and error-prone.
PivotTables automate many aspects of creating frequency distributions and cross tabs.

CREATING FREQUENCY DISTRIBUTIONS WITH PIVOTTABLES

You want to create a frequency distribution for Location.
Earlier, we saw how to do it manually.
Now we will see an automated way to do this—PivotTables.
PivotTables automate the creation of both frequency distributions and crosstabs.

START THE PROCESS

1. Click anywhere in the table.
2. Go to the Insert tab.
3. Click on PivotTable.
4. Select PivotTable

Page 16
Descriptive Statistics Lab

NEXT STEP

A Create PivotTable dialog box appears.
The table should be selected automatically.
Select whether you want the PivotTable
placed on a new worksheet (selected) or on an
existing worksheet, such as the current
worksheet.

YOU CAN NOW BUILD THE PIVOT TABLE

You want the row
labels to be the
locations.
So drag
Location
to the Row
Labels
box at the
bottom.
You want the
second column to
be a count of
people in each
Location row.
So drag
Location
to the Σ
Values                    The row labels are
box.                      Location values.
By                        The count is for Location
default,                  values.
the Count
for
Location
values.

Hands On: Create this frequency distribution.
Using a PivotTable, create a frequency distribution for how many people are men and women.

Page 17
Descriptive Statistics Lab

Using a PivotTable, create a frequency distribution for how many people are men and women.

ISSUE

Do women make less than men?

Click
anywhere in
the table.
Under the
Insert tab,
click on
PivotTable
and select
PivotTable.
You want the
rows to be
gender
category, so
drag Gender
to the Row                  The row labels are
Labels box.                 Location values.
For each                    The sum is for Income
gender you
values.
want the
average
salary. Drag
Income to the
Σ Values box.
However, the
selection is
Sum of
Income, not
average.

Hands-on: Create this frequency distribution.

Page 18
Descriptive Statistics Lab

CHANGE SUM TO AVERAGE

But to compare salaries for men and women, we need averages, not sums because there are
different numbers of men and women.

Right click on the Sum of Income
arrow.
Select Value Field Settings.

In the Value Field Settings dialog box,
select Average (mean).

The second column changes to average
income.

Women make more than men, on average.
Hands On: Try it.
Then create a frequency distribution for average Income by Location.
Then create a frequency distribution for average Age by Gender.

Page 19
Descriptive Statistics Lab

CROSS TABS WITH PIVOT TABLES

You want to see how the number of years the employee has been with the firm differs between
men and women. You want a table like this:

Years with Firm               Males       Females

1

2

…

Now there are both row and column labels.

START A CROSSTAB PIVOTTABLE.

Use the steps described above.

DEFINE THE PIVOTTABLE

Drag Years with
Firm to the Row
Labels box.
Drag Gender to the
Column Labels box.
Drag anything to the
Σ Values column.
You are only doing
counts, so it doesn’t
matter with one you
count.
Change the Value
Field Settings for the
Σ Value box to
This PivotTable is a cross
Count.                           tab.
You get the
indicated Cross Tab.

Hands On: Do it.
Then create a cross tab to show how average Income varies by Gender and Years with the Firm.
Then create a cross tab to show the average age by Location and Years with the Firm.

Page 20
Descriptive Statistics Lab

Part 5: Analysis Tool Pack and Descriptive
Statistics
ANALYSIS TOOL PACK

The Excel Analysis Tool Pack is an add-in that comes with Excel. We will use it extensively in
this class. You may have to add it.

First, see if the Analysis Tool pack is
1. Click on the Office Button.
2. Click on Excel Options at the bottom of

3. On the Excel Options dialog box, select
4. See if the Analysis Toolpack has been

IF SO, YOU ARE FINISHED.
If not, go on.

5. Go to Manage, Select Excel Add-Ins, and
Click on Go.
may need your Excel program disk.

Page 21
Descriptive Statistics Lab

STATISTICS FUNCTIONS

ON THE FORMULAS TAB

1. Go to the Formulas tab.
2. Select More Functions.
3. Select Statistical.
4. Select the function.

WHENVER YOU ENTER A FORMULA

1. When you type an equal
sign to begin entering a
formula, fx appears,
indicating that you may
select a function.
2. When you click on fx, the
Insert Function dialog box
appears.
3. You can search for a
function by giving a
description of what you
want to do and then hitting
Go.
Or
4. If you know the name of
the function, select the
Statistical category, select
the function.
5. Excel gives you a short
description of its syntax and
what the function does..

Page 22
Descriptive Statistics Lab

Part 6: Charts
Single statistics, frequency distributions, and cross tabs present information as numbers and
tables. Charts present information graphically.

DATA

Here is some data we wish to chart.
The categories are hardware, software, and service.
The data we wish to chart is revenues.
Category        Revenues
Hardware              500
Software            1,700
Service               900

BEGIN THE PROCESS

1. Select the chart—
Although usually
you can click a
cell anywhere
inside the table.
2. Click on the Insert tab.
3. Look at the Charts
option to see the kind of
chart you may create.

SELECT THE SIMPLEST COLUMN CHART TYPE

4. Select a column chart.
5. Select the simplest bar chart.

Page 23
Descriptive Statistics Lab

THE RESULT

Excel creates the following bar chart.
You can take it as it is, or you can
make changes to fix it or improve it.

MODIFYING THE BASIC CHART

TERMINOLOGY

To modify the chart, you must be familiar with several pieces of terminology found in most
charts.

BASIC EDITING

You can right click on numbers to format them.
You can double-click on test to change it (titles, etc.)

Page 24
Descriptive Statistics Lab

THE DESIGN TAB

Whenever you click anywhere in a table, three additional tabs appear.
You typically use them left to right.
The Design tab helps you refine your initial design.

Type       Change Chart       The most basic option—changes the entire chart type.        Try changing it to a line
Type                                                                           chart.
Save as a          Something you really may want to do at the end—so           Not on an exam or
Template           that future charts can be build with this template.         homework.
Data       Switch             Discussed later.
Row/Column
Select Data        Discussed later.
Chart      (Visual Choices)   Excel begins with a default design for the elements to be   Play around with it.
Layouts                       included and how they are shown. Chart layouts allow
you to try different overall layouts (designs).

The last choice gets you back to the default.
Chart      (Visual Choices)   This pretties up the basic layout you have chosen. Be       Play around with it.
Styles                        careful. You can easily overdo it. Also, some color
combinations print poorly in black and white.

Location   Move Chart         You can move the chart to another sheet or to a new         Not on an exam or
sheet.                                                      homework.

Page 25
Descriptive Statistics Lab

THE LAYOUT TAB

The design tab lets you select the general overall design.
The layout tab lets you specify individual elements of the layout in more detail.

Stuff               We will ignore.                                                            Ignore.
on the                                                                                         Will not
Left                                                                                           be on
homework
or an
exam.

2.       Chart      Controls whether the chart title shows or not and if so, where it is       Play with
Labels   Title      located.                                                                   this.

Axis       Controls whether the primary horizontal or vertical axis shows or not      Play with
Titles     and if so, where it is located.                                            this.

Legend     Controls whether the legend shows or not and if so, where it is located.   Play with
this.

Data       Controls whether data labels show or not and if so, where they are         Play with
Labels     located.                                                                   this.

Data       The data table shows the data on which the chart is based.                 Play with
Table                                                                                 this.
Controls whether the data table shows and if so, where it is located.

Page 26
Descriptive Statistics Lab

THE LAYOUT TAB (CONTINUED)

The design tab lets you select the general overall design.
The layout tab lets you specify individual elements of the layout in more detail.

3. Axes   Axes

3. Allows you to choose whether to show a vertical (or
horizontal) axis.
4. Allows you to show your figures in thousands, millions, or
billions, so that your numbers do not get to large.
5. More Primary Vertical Axis Options is important.

Page 27
Descriptive Statistics Lab

THE LAYOUT TAB (CONTINUED)

The design tab lets you select the general overall design.
The layout tab lets you specify individual elements of the layout in more detail.

3. Axes   Axes

2. Axis Options allows you to specific minimum and maximum
values to be shown on the axis. Useful if Excel picks odd choices.
2. Allows you to control the major units shown, so you do not
have too many or two few.
3. Allows you to display units (hundreds, etc.)

Page 28
Descriptive Statistics Lab

THE LAYOUT TAB (CONTINUED)

Axes   Alignment                                                                     Try this on the
horizontal
axis.

Under Alignment (1),
2. You can make the text horizontal or vertical.
3. You can make it show at an angle (often done on the
horizontal axis.

Guidelines   Turns horizontal and vertical gridlines off and on and adjusts   Ignore. Not on
their spacing.                                                   homework or
exams.

Page 29
Descriptive Statistics Lab

THE LAYOUT TAB (CONTINUED)

4.       Plot Area      For filling in the plot area.
Back-
ground

More Plot
Area Options

The Result

Stuff                                                             Nothing on the
on the                                                            exam or
right.                                                            homework.

Page 30
Descriptive Statistics Lab

CHART TOOLS/FORMAT

The design tab lets you select the general overall design.
The layout tab lets you specify individual elements of the layout in more detail.
The format tab allows you to format individual items in detail.

Here the vertical grid text has been formatted.
You can also play with gridlines.
You can also play with the data series.

The design tab often overdoes things. You are not required to know it for homework or exams,
but it’s kind of cool.

Page 31
Descriptive Statistics Lab

PIE CHARTS

Everybody seems to love pie charts.
They show how each category’s size relates to the total. (The whole pie?)
Unlike bar charts and line charts, pie charts can only show data from a single data series.

CHOOSING A DATA SERIES

We decide to select Year 2 for our data series.
Consequently, our category values are not contiguous with (next to) the data values.

Specifying the chart takes three steps.
1. Select the category data, including the header row. This will be the horizontal axis.
2. Hit control.
3. Select the data series value including then header row.

PIE CHART

After you have selected the table information, hit insert and then pie chart.

Page 32
Descriptive Statistics Lab

REDOING THE PIE CHART

Delete the pie chart you created.
This time select a 3D chart.

UNDER DESIGN, SELECT A STYLE

Page 33
Descriptive Statistics Lab

Under Format, chose Data Labels.
Select “Outside End”

PULL A SLICE OUT FOR EMPHASIS

Click on a slice you wish to
emphasize.
Pull it away from the center, slowly.

Page 34
Descriptive Statistics Lab

DATA TABLES

Under Chart Tools / Layout, click on Data Table.

Here, both data labels and data tables are shown. This is overkill.

Page 35
Descriptive Statistics Lab

NUMERICAL CATEGORIES

Excel generally makes smart decisions when it does charting.
However, when the horizontal axis categories are numbers, such as years, Excel becomes stupid.

1. Here is the
table to be
graphed.
Select it and
select chart type
bar chart.

2. This is what
you get.
It assumes that the
Year column
holds data.
This is not what
you want.

3. Under the
Design tab Data
group, choose
Select Data.

Page 36
Descriptive Statistics Lab

4. You get the
following dialog
box.
Year is incorrectly
in the legend
category.
Click on Year.
Click on Remove
to remove it.

5. Now you need
values to the
Horizontal
(Category) Axis
Labels.
Click on Edit.

6. The Axis
Labels dialog box
appears.
Select the Year
values (NOT
including the
Click on OK.

Page 37
Descriptive Statistics Lab

entries now
include Product A
and Product B.
The Horizontal
Axis now has
years.
Click on OK.

8. Finally, this is
the chart you
want.

Page 38

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 1 posted: 8/9/2012 language: pages: 38
How are you planning on using Docstoc?