Basic statistics using for use with statistical techniques in business & economics

Statistics Using

for Office XP

for use with

Lind | Marchai | Wathen

Prepared by

Ronald Merchant

Renee Goffinet

Virginia Koehler

Basic Statistics Using

Excel® for Office XP®

for use with

Statistical Techniques in

Business & Economics

P repared by

R onald M erchant

U niversity o f P h o en ix

R enee G offinet & V irg in ia K oehler

S p o ka n e F a lls C o m m u n ity C ollege

Boston Burr Ridge. IL Dubuque, /A Madison, W! New York San Francisco St. Louis

Bangkok Bogotá Caracas Kua/a Lumpur Lisbon London Madrid Mexico City

Milan Montreal New Delhi Santiago Seoul Singapore Sydney Taipei Toronto

Thirteenth Edition

C oa sta l C arolina U niversity

McGraw-Hill

Irwin

Ị dạ; HỌC THẨĨ NGUYÊN

1 TR Ü N 'G T A M H Ọ C L IẸ U

McGraw-Hill

Irwin

Basic Statistics Using Excel® for Office XP® for use with

STATISTICAL TECHNIQUES IN BUSINESS & ECONOMICS

Douglas A. Lind, William G. Marchal, Samuel A. Wathen

Published by McGraw-Hill/Irwin, an imprint of The McGraw-Hill Companies, Inc., 1221 Avenue o f the

Americas, New York, NY 10020. Copyright © 2008 by The McGraw-Hill Companies, Inc. All rights reserved.

No part o f this publication may be reproduced or distributed in any form or by any means, or stored in a

database or retrieval system, without the prior written consent o f The McGraw-Hill Companies, Inc., including,

but not limited to, in any network or other electronic storage or transmission, or broadcast for distance learning.

3 4 5 6 7 8 9 0 QPD/QPD 0 9 8

ISBN 978-0-07-303026-5

MIlID 0-07-303026-0

PREFACE

Basic Statistics Using Excel for Office XP is a workbook, which empowers students to use the computer

to help them understand and apply the basic tools taught in an introductory statistics course. When

students use Excel to experiment and illustrate their problems, they can better visualize them and more

easily see what happens.

Given the popularity o f Excel and its expanded capacity to handle statistical data, it is a natural in

colleges; in fact, many colleges have Excel on all o f their computers. In addition, Excel is the software o f

choice in today’s business world. What students learn with Excel can often be applied immediately on

the job as well as in other classes. Many students have been exposed to this powerful software program

and may already have it on their own computers.

This workbook is especially designed to accompany the Thirteenth Edition o f Statistical Techniques in

Business and Economics by Douglas A. Lind, W illiam G. M archal, and Samuel A. Wathen. It also

works well with the Fifth Edition o f Basic Statistics for Business and Economics by Lind, M archal, and

Wathen. Both o f these books use Excel and Minitab illustrations. They sometimes use only Excel on

problems where the authors feel Excel is superior and Minitab where they feel Minitab is superior. This

workbook fills a special niche for instructors who use only Excel in their courses. It can also be used

as a companion to most other introductory statistics texts, or by itself.

The chapter goals listed at the beginning o f each chapter provides overviews o f the main topics covered

and the tasks students should be able to do after having worked through the chapter.

Following each chapter are several exercises to provide additional practice in applying the topics covered.

Thus the students can check their comprehension o f the material as they progress through each chapter.

These exercises can also be used as class assignments.

The illustrations in this workbook are from Excel XP 2003; however, the material has been used

successfully with previous versions o f Excel. Excel XP is fully backwards compatible with Excel 2000.

This means that you can open, edit, and save files between both versions (XP and 2000), w ithout having

to save them as a previous version.

If you are using Excel 2003, you should not have any problems using this workbook.

Ronald Merchant

Renee C. GofTinet

Virginia E. Koehler

ACKNOWLEDGMENTS

W e are grateful to many people for the help and encouragement throughout the development o f this

workbook: M ike Antonucci, Gail Korosa and Christina Sanders at M cGraw-Hill Irwin, our students for

their patience while classroom testing our rough drafts, and the feedback from several reviewers. Julie

Sanborn’s editing skills improved the earlier versions o f this manuscript immensely.

W e welcome comments about the book and suggestions for improvement:

Ron M erchant

4501 East W alnut Road

Gilbert, AZ 85297

rmerch@ qwest.net

CONTENTS

1 U sing M icrosoft E xcel S p rea d sh eets.......................................................................................................1

2 D escribing D ata: Frequency D istributions and G raphic P re se n ta tio n s....................................13

3 D escribing D ata: N um erical M e a su re s.................................................................................................33

4 D escribing D ata: D isplaying and E xp lo rin g D a ta ............................................................................49

5 A S u rv ey o f P robability C o n c e p ts......................................................................................................... 59

6 D iscrete P robability D istrib u tio n s......................................................................................................... 67

7 C o ntinuous Probability D istrib u tio n s................................................................................................... 79

8 S am p lin g M ethods and the C entral Lim it T h e o re m ........................................................................91

9 E stim ation and C onfidence In te rv a ls..................................................................................................109

10 O n e-S am p le T ests o f H y p o th esis......................................................................................................... 119

11 T w o -S a m p le T ests o f H y p o th esis........................................................................................................ 141

12 A nalysis o f V a rian ce.................................................................................................................................161

13 L in ea r R egression an d C o rre la tio n ......................................................................................................175

14 M u ltip le R egression and C orrelation A n a ly sis............................................................................... 183

15 Index N u m b e rs............................................................................................................................................ 191

16 T im e S eries and F o rec astin g .................................................................................................................. 197

17 N on p aram etric M ethods: C hi-S quare A p p lica tio n s.......................................................................205

18 N on p aram etric M ethods: A nalysis o f R anked D a ta ......................................................................2 23

19 S tatistical P rocess C ontrol and Q uality M an a g em en t................................................................... 233

C hapter 1

CHAPTER

1

USING MICROSOFT EXCEL SPREADSHEETS

CHAPTER GOALS

A fter completing this chapter, you will be able to:

1. Understand why Excel is so useful as a statistical tool.

2. Define what is meant by a spreadsheet.

3. Enter data into a spreadsheet.

4. Create formulas and solve problems with a worksheet.

5. Edit data that is in a spreadsheet.

6. Use a spreadsheet to experiment and illustrate.

Introduction

W elcome to Basic Statistics Using Excel.

Excel is the most popular spreadsheet program in the world and has the capacity to handle a wide variety

o f statistical applications. Most colleges have Excel on their campus computers; it is part o f the M icrosoft

Office package. You may have already had some exposure to Excel and used it for other applications.

You may even have Excel on your home computer.

Using Excel will enhance your ability to understand and apply statistical principles. It is the software

choice in the business world.

Basically, spreadsheets are used to help you with analysis o f numerical data and to solve problems. In a

spreadsheet, you can enter data that is related, and see what the results are if you change that data. You

can create charts and graphs. You can run statistical analysis. Spreadsheets are used in businesses by

managers to assist in decision making.

This chapter is for those who have never used a spreadsheet or worksheet, or for those who want a review

o f the basics.

C hapter 1

Open Microsoft Excel. You may need to ask how the system you are using operates. A task pane opens

on the right side o f the worksheet window, providing you with document options: Open a workbook, New

from existing workbook, and New from template. You may close the task pane to provide more

workspace on your screen by selecting View , Task P an e from the M enu bar.

The first row o f your M icrosoft Excel w orksheet is called the Menu bar. The second and third rows are

called Tool bars. You will often be requested to select a button from either the M enu bar or the Tool bar.

I Q 0 e 6 * ï®” Insert Format Tods Data J$ndow a » y j j a i *

||d E g a l a i ? ] » * a. it aim-Iff

|| A.iai_______________.10 » | b / u | g g s B | æ % , 'A

As your mouse pointer rests on each button o f the Tool bar, a short description is displayed ju st below

that button.

Creating a New W orksheet

If a blank worksheet does not appear, you will need to create a new worksheet.

To create a new worksheet select File, from the M enu bar. Select New. O r you may select

the button for a new page.

The worksheet consists o f rows, colum ns and cells. Each individual rectangle is a cell. Each cell is

identified by it placement in the Colum n (A, B, C ...) and the Row (1, 2, 3 ...). Thus, the cell B3 w ould be

in the 2nd column and the 3rd row. The m ouse pointer in Excel looks like an open plus sign. W hen the ç

pointer is on a cell, click the left mouse button and that cell becomes the active cell. The cell will have a

dark bordered box around it. You can also use the arrow keys: up, down, right, left, to move around in

the worksheet.

2

Chapter 1

zssjsnzsMmÊOÊmmmmcm»':-?

J Bte £dt SJew Insert Format look Bata ffindow üe*P .'^Type àqu^tenfor help » . S X

! 0 ( * H d « r « Q t » - a .

-10 • [b] y v (.• « V g * % , a iü : « * : _ - * • & • . t__________________________

r

Minimize

Maximize

Exit Excel

H t Q ' j n j x j — Titlèbar

— Main Menu

— Standard Tool

Formatting Tool bar

Formula bar

(W orksheet with cell B3 as active cell)

The data or information you key will show in the active cell and in the formula bar. W hen you press the

<Enter> key, your data is entered into the cell and the cell immediately below becomes the active cell.

O r you can point to another cell or use your arrow keys. To edit a cell, double click your mouse pointer

in that cell, and the cell can be edited. The mouse pointer will show as a large I instead o f an open plus.

You may then edit the cell, without rekeying the entire contents.

You can select several cells at once to work with, called a range. A range is a rectangular group o f cells.

To use your mouse, you would place your mouse pointer on the upper left cell o f the range to be

highlighted, then click, hold and drag your mouse pointer to the lower right cell o f the range, and then

release the mouse button. The first cell shows a white background, all other cells in the selected range

show a black background. A range is identified by its first and last cells with a colon in between.

1. A ctivate cell A1 by clicking on it, key S c h o o ls and press <Enter>.

2. Cell A2 should now be active. Key R o ad s. Key in the remaining data so that your

worksheet looks like the one on the next page. Use the incorrect spelling o f Supplie in cel! A4.

If you make an error, you can correct it by immediately selecting Edit from the Menu bar,

and selecting Undo, or you can simply click on the Undo button.

3

C hapter 1

insert Fgmat Iools Bata ÿflndow Heto Type a question for help a x

I Arial , 1 0 I B I Ï f 8 1 |

• - % Z • S t 51 n A @ .

* % , a .■s 1 - 3 <»

B1 fi. 440000

i A B C I 0 I E F G 1 H | 1

I f a Schools 440000 [ | ~

1160000

m 320000 1 | |_________ [ _ _

1 4 £ Supplie __ 6ÜÜOO i i I i i -__

r i i .

(W orksheet with range o f cells B1 :B4 selected)

To move text to a new position

1. Highlight the cells, click on the Cut button.

2. Select the new location by activating the cell in which you wish

the text to be located, click on the Paste button.

If you wish to copy text, follow the same procedures for moving text, but instead o f the Cut

1 button, select the Copy button.

Excel allows you to check the spelling o f the text in your entire workbook or just selected cells. However,

'worksheets are often created using abbreviations which this feature may not recognize. If this happens

; you can simply select Ignore.

1. To check the spelling o f the whole workbook simply click on the Spelling | * K ' |

button. A dialogue box will appear. U u

¡Depending on the cell that is active when you select the Spelling button, you m ay receive the following

(dialogue box. If you have not checked for spelling yet, then select Yes.

■ W ^ x l

Do you want to continue checking at the beginning of the sheet?

C hapter 1

2. Click on Supplies and select C hange. Select OK.

Not In dictionary:

. |Supple Ignore Once |

Suggestions:

Ignore Al |

Add to Dictionary |

Supplied

Supples

Supply

Supple

d

£hang® |

Change A| |

AutoCop |

Dictionary language: ¡English (U.S.) - |

Options... | 'Undoies!/ | Cancel |

To check a selected area, highlight the cell or cells you want Excel to check, click on the

Spelling button, and follow the same procedure as above.

You may create your worksheet in either portrait or landscape orientation by doing the following:

1. From the M enu bar, select File. Select P ag e Setup. A dialogue box appears with four tabs.

2. The P ag e tab should already be selected. You may select the radio button for Portrait or

Landscape. Select OK.

£P ?£_]| Margins | Header/Footer | Sheet |

Orientation .Æ. - ■ — |—

[ a ] Poftra* I A l r Landscape

Erint... 1

Print Preview |

(• Adjust to: |l00 normal sfee

re tto : fi 3 page(s) wide by fi ^ tal

Paper sije: |letter

Print guatty: [600 dpi ~ 3

FHtst page number: | Auto

f OK | Cancel |

N O T E : As you work through this text, some illustrations may appear different than your screen.

5

Saving a W orksheet

To save a worksheet (file) do the following:

1. Select File, from the M enu bar. Select S a v e As.

2. Key in the desired file name in the File nam e text box, key T a x D o llars.

C hapter 1

Save(n: | St 3'A Floppy (A:) »

Æ

History

3

My Documents

£)Bar Chart

Histogram

® Line Chart

Qpie Chart

Desktop

Favorites

w * File name: |Bookl| z i 1 Hm

Places Save as type: ¡Microsoft Excel Workbook * | Ow*

3. At the top o f the dialog box, click on the down arrow at the right o f the S ave in: text box.

Click your mouse on 3 /2 Floppy (A:).

4. Click on Save.

Closing a W orksheet

To close a worksheet (file) do the following:

1. Select File, from the M enu bar. Select Close.

2. If no changes have been made since the last save, the file will close and the screen will be

blank.

3. If the current information has not been saved you m ay select Y e s to save the changes, select

No if you do not want to save the changes, or select C ancel to go back to the current

worksheet.

A fte r you close a worksheet, you need to create a new w orksheet or retrieve an existing w orksheet to

continue working.

6

Chapter 1

Retrieving a W orksheet

1. Select File from the Menu bar. Select O pen.

You may also open an existing document by selecting the Open icon on the Tool bar.

Look in: |.jt 3'A Floppy (A:) j r j * . I t] 10 K C | i l : Tools’

i i 2 J

History

^)Bar Chart

%1 Histogram

® Line Chart

S^Pie Chart

My Documents

u

Desktop

[ ¿ ]

Favorites

B P

My Network

File Qame: || z l 1 3 » " . ' I

Files of type: | AH Microsoft Excel Files

................ ¿ 1 : Cancel |

2. Verify the location o f the file you wish to retrieve. You may have to change the selection in

the Look in: text box at the top o f the active window. The arrow to the right o f the text box

will allow you to access a pull down list o f file location options.

3. Highlight the file that you wish to retrieve by placing your mouse curser over the file name

and click the left mouse button once.

If you do not see your file located in this folder, try selecting All F iles from the F iles o f type: drop down

list at the bottom o f the dialogue box.

4. Once the file is highlighted, select O pen.

5. A fter making any changes, save and close the worksheet.

7