Return to
BA201
BA321
College of Business
Northern Arizona University
e-mail and WebMail

BA201 and BA321

Descriptive Graphs: Excel and Equations

Excel- commands used in this topic in Office 97 Office 2000

Frequency Distributions Histograms Frequency Polygons

Ogives Pie Charts Nominal Data Equations


Excel Steps

 

Frequency Distributions

(1) Use the steps to get a histogram. The frequency distribution will be a part of that output. If you do not want the histogram to show up, do not click Chart Output. Click on Tools-Data Analysis-Histograms. If Data Analysis does not show up, then click on Tools-Addins and select the top two options of Analysis Pack and Analysis Pack VBA.

(2) Create Bin value equal to the upper class limit (UCL) of each class. Highlight the cells next to the Bin values plus one cell. Click Paste-Function-Statistical-Frequency. Fill in the arrays in the box. While the box is still opend press Control-Shift-Enter. See (1) above for how to get a frequency distribution as part of a histogram output..


 

Histograms

Click on Tools-Data Analysis-Histograms. Select Chart output. If Data Analysis does not show up, then click on Tools-Addins and select the top two options of Analysis Pack and Analysis Pack VBA. (See Frequency Distributions above.) In the hitogram dialog box, you will find the following options:

About the Histogram dialog box

Input Range
Enter the reference for the range of data you want to analyze.

Bin Range (optional)
Enter the cell reference to a range that contains an optional set of boundary values that define bin ranges. These values should be in ascending order. Microsoft Excel counts the number of data points between the current bin number and the adjoining higher bin, if any. A number is counted in a particular bin if it is equal to or less than the bin number down to the last bin. All values below the first bin value are counted together, as are the values above the last bin value.

If you omit the bin range, Microsoft Excel creates a set of evenly distributed bins between the data's minimum and maximum values.

Labels
Select if the first row or column of your input range contains labels. Clear this check box if your input range has no labels; Microsoft Excel generates appropriate data labels for the output table.

Output Range
Enter the reference for the upper-left cell of the output table. Microsoft Excel automatically determines the size of the output area and displays a message if the output table will replace existing data.

New Worksheet Ply
Click to insert a new worksheet in the current workbook and paste the results starting at cell A1 of the new worksheet. To name the new worksheet, type a name in the box.

New Workbook
Click to create a new workbook and paste the results on a new worksheet in the new workbook.

Pareto (sorted histogram)
Select to present data in the output table in descending order of frequency. If this check box is cleared, Microsoft Excel presents the data in ascending order and omits the three rightmost columns that contain the sorted data.

Cumulative Percentage
Select to generate an output table column for cumulative percentages and to include a cumulative percentage line in the histogram chart. Clear to omit the cumulative percentages.

Chart Output
Select to generate an embedded histogram chart with the output table.


 

Frequency Polygons

Click on the Chart Wizard button-XY Scatter- Scatter connected dots.


 

 

Ogives

Click on Tools-Data Analysis-Histograms. Select Cumulative Percentage and Chart output. If Data Analysis does not show up, then click on Tools-Addins and select the top two options of Analysis Pack and Analysis Pack VBA. Select Cumulative Percentage and Chart. (See Frequency Distributions above)


 

 

Pie Charts

Click on the Chart Wizard button-Pie.


 

 

Nomial Data, Frequency Distribution, Bar Chart and Pie Chart

Frequency Distribution: Put nominal data into a single column. Click Data-Pivot Table Report. Select Microsoft Excel list. Select all of the nominal data including the title. Drag "Data" (assuming you have used Data as the title of the nominal data) to inside the "row" area and to inside the "data" area.

Bar Chart: Click the Chart Wizard-Bar. Include the nominal data and the numerical data from the frequency distribution for series

 Pie Chart: Click the Chart Wizard-Pie. Include the nominal data and the numerical data from the frequency distribution for series. (See Pie Chart above)


 

Descriptive Graphs

Equations


  Class width Relative frequency

Cumulative relative frequency  

Class midpoint    


 

1. Class width

CW = (H - L) / k

High = H

Low = L

k = #classes

 

2. Relative frequency

rf = f / n

f = frequency

n is the number of data values

 

 

3. Cumulative relative frequency

crf =  Srf

 

4. Class midpoint

CM = (LCL + UCL) / 2

LCL = Lower Class Limit

UCL = Upper Class Limit


Put "BA201- your section number" or BA321 and your last name somewhere in the subject line for your e-mail.

e-maile-mail and WebMail

Back to top


Copyright 2000 NAU and CBA
ALL RIGHTS RESERVED
Dr. James V. Pinto