Topic:Introduction to Excel for Statistics

From SharedExperienceProject

Contents 1 Topic Highlights 2 Introduction and Motivation 3 Learning Activities 4 Learning Objectives 5 Exercises 5.1 What is Excel? 5.2 Getting Started 5.3 My First Excel Spreadsheet 5.4 My First Statistical Analysis 5.5 Better than a Calculator? 5.6 Best-Practices 5.7 Finding and using Megastat 5.8 In Closing 6 References

Topic Highlights

(What you will learn)

• The basics of how Microsoft Excel is used for statistics
  
• How to access the macros provided found in the lab
  

Introduction and Motivation

(Why learn it)

Not only is Microsoft Excel a very useful tool for statistical analysis, but it will be used extensively in the course Business Statistics I. If you have not already become familiar with it, you will need to do so.

Learning Activities

(What I need to do to meet the objectives given below)

Learning activities for this topic

Type	Name	Direction
In-class activity	Work through the exercises below	Peer-directed
Review and reflection	Test yourself against the learning objectives below	Self-directed

Learning Objectives

(Levels of understanding to be gained)

Learning objectives for this topic

Level of Understanding	Objective(s) (presented as self-assessment questions)
Very best	Could I teach a friend the basic Excel functionality we saw in the tutorial exercises?
Highly satisfactory	Can I explain to a friend why Excel is important to businesspeople? Can I demonstrate, with an example in Excel, why it is more useful than a calculator in many cases?
Satisfactory	Do I know why Excel is more useful than a calculator in many cases, i.e. could I write down the reasons if someone asked me to? Can I do analyses of the types done in the tutorial exercises, without looking at the tutorial notes? Do I apply the best-practices outlined in the tutorial?
Maybe just enough to pass	Can I find, start and exit Excel? Can I start a new Excel spreadsheet? Can I open an existing Excel spreadsheet? Was I able to make it through the tutorial exercises?

Exercises

What is Excel?

• "Excel" is the common, shortened name for a computer program called Microsoft Office Excel that is used in businesses around the world for analyzing, sharing, and managing information to make more informed decisions^[1].
  
• It is an interactive spreadsheet, which means that it can be used to build a model that can be reused for future analyses. This is unlike most calculators, which require you to enter the data every time you do an analysis, and which do not usually allow you to save the mechanics of the analysis for later use.
  
• Excel is also very useful for displaying data and the results data analyses.
  
• In statistics, Excel is particularly useful because of the statistical functions that are built into the package. These save lots of time, as we will see in the examples that follow.
  

Getting Started

Let's start by opening and closing an Excel worksheet.

1. Open Excel:

• From the taskbar, choose Start > All Programs > Microsoft Office > Microsoft Office Excel 2003
  
• A window will appear with an empty worksheet

2. In Excel, each file is called a workbook and contains one or more worksheets. Identify the following parts of the empty worksheet in front of you:

• The title bar, which contains the control menu, the program name, the file name and the minimize and maximize buttons
• The menu bar, which contains the commands with drop down menus
• The standard toolbar, which contains basic functions such as opening and closing files
  
• Other toolbars, such as the formula bar and the formatting bar
• The spreadsheet itself, containing columns, column headings, rows, row headings, cells, and the scrolling bar
• The spreadsheet tabs, which allow you to navigate between tabs of the current worksheet
• The status bar, which contains information about the task you are currently doing

Answers can be found on Blackboard in the document entitled Intro to 'Excel for Statistics.pdf'.

3. Close Excel:

• Use the mouse to select File > Exit from the menu bar

My First Excel Spreadsheet

Now let's create a simple spreadsheet.

1. Repeat step 1 above to open a new worksheet.

2. Save the worksheet:

• Select File > Save As from the menu bar
  
• Choose an appropriate location for the file on your computer's hard drive or on your network drive
  
• Enter the filename "A Sampling of My Neighbors.xls"
  
• Press the button marked Save
  

3. Enter the column headings, so your worksheet looks like the one below:

Sample 1

4. Poll four of your classmates to find out their names, ages, numbers of siblings and the number of coffees they consumed yesterday. Enter this data in the appropriate cells in your spreadsheet. (See Sample 2 below if you need to see what this looks like.)

5. Give the current sheet a good name:

• Right-click on the spreadsheet tab labeled "Sheet 1"
  
• Select "Rename"
  
• Give it a name such as "Some data about my classmates"

6. You should save your worksheet again now, which should like like the one below:

Sample 2

My First Statistical Analysis

So far, you've collected and entered some data. Next, treat this like a mini-survey and carry out a simple analysis of the data. We are going to compute the minimum and maximum of your neighbors' ages, the average number of siblings they have, and the total number of coffees they consumed yesterday.

1. It is good practice to enter descriptive text fields such as "The youngest classmate is:", "The oldest classmate is:", "The average number of siblings is:" and "The total number of coffees consumed is:". Do this in appropriate cells below the data.

2. It is also good practice to indicate the units of measure you used in your data collection, e.g. years, siblings, and cups. Do this in the appropriate cells, leaving cells blank for the actual analysis, as shown below:

Sample 3

3. Now compute the youngest neighbor:

• In the appropriate cell (cell D7 in the above example), type the following:
  
  • =MIN(B2:B5)
    
  • You may need to change the values B2 and B5 to correspond to the cells that bound your age data
    
  • Be sure to include the = sign
    
• The smallest of your neighbors' ages should appear in cell D7
  

4. Now compute the oldest neighbor:

• In the appropriate cell (cell D8 in the above example), type the following:
  
  • =MAX(B2:B5)
    
  • Again, you may need to change the values B2 and B5 to correspond to your spreadsheet
    
• The largest of your neighbors' ages should appear in cell D8
  

5. Now compute the average number of siblings your neighbors have:

• In the appropriate cell (cell D9 in this example), type the following:
  
  • =AVERAGE(C2:C5), again changing the values if necessary
    
• The destination cell (D9 in this example) should now contain the average number of siblings your neighbors have
  

6. Finally, compute the total number of coffees they consumed as a group:

• In the appropriate cell (cell D10 in this example), type the following:
  
  • =SUM(D2:D5)
    
• The destination cell (D10 in this example) should now contain the total number of coffees your neighbors consumed yesterday
  

7. You should save your worksheet again now, which should like like the one below:

Sample 4

Better than a Calculator?

In business statistics, there are at least three reasons why Excel is better than a calculator:

The first is that managers can more easily inspect their data and their analysis for errors.

The second is that they can make use of the formulas built into Excel, which save a considerable amount time that would otherwise be spent punching of calculator keys. Recall that we did this above by using the built-in MIN(), MAX(), AVERAGE(), and SUM() functions.

The third is that it is much easier to repeat an analysis when additional data points become available at a later date. To convince yourself of this, carry out the following steps to modify your spreadsheet:

1. Insert a blank row between the data for your second and third people:

• To do this, right-click on the left side of the fourth row and select Insert, as shown below:

Sample 5

2. Add data for yourself in the new row:

• Enter your name, your age, the number of siblings you have, and number of coffees you consumed yesterday, to update the appropriate columns of the spreadsheet as shown below:

Sample 6

3. Observe how (some of) the cells have changed automatically for the youngest, oldest, average number of siblings and total number of coffees consumed. Your data is now included in the analysis. How long would it have taken you to recompute those values using a calculator?!

4. Now imagine how useful Excel is in a business setting. A manager deals with very large amounts of data every day, but the analyses he or she does are very often similar from one day to the next. By building a model within a standardized spreadsheet, Excel can be used to save very large amounts of time. Even based on this simple example, it's not hard to imagine how the manager will be better at analyzing, sharing, and managing information to make more informed decisions

Best-Practices

In the above examples, you should have noticed the use of the following best-practices:

• Using column headings to identify your columns of data, e.g. Name, Age, Num siblings and Num coffees
  
• Using descriptive fields to clearly identify the output of your analyses, e.g. "The youngest classmate is:"
• Specifying the units of the output of those analyses, e.g. years, siblings, cups
• Naming your worksheet appropriately, e.g. "Some data about my classmates"
• Saving your worksheet often so you don't lose any data
  

Finding and using Megastat

In addition to the functions that come with Excel, we will use a number of the MegaStat functions that are provided in the macros on the CD that comes with your textbook. Those macros are already installed in the lab.

Your last step is to find the MegaStat macros and use them to compute the average number of siblings. Do this as follows:

1. In Excel go to the menu item Add-Ins
2. Select MegaStat
3. Select Descriptive Statistics...
4. With the cursor in the Input range field select the cells corresponding to the sibling data, as shown below
5. Press OK

Sample 7

You've completed your first MegaStat computation! You should now see a new sheet with the data below:

Sample 8

We'll take a closer look at MegaStat in future lab sessions. In the meantime you may wish to check out the tutorial document found on Blackboard (Getting Started with MegaStat.pdf).

In Closing

Other tutorials are available for the use of Excel in statistics. Some are listed here:

• Microsoft tutorial on Excel's statistical functions
• Microsoft tutorial on simple calculations and entering formulas

References

1. ↑ Excel Homepage (2007) Accessed on 2007-08-27.