Return
to
|
Return
to
|
|
|
|
|
|
|
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-mail
and 
Copyright
2000 NAU and CBA
ALL RIGHTS RESERVED Dr.
James V. Pinto
