How to Construct an Ogive Graph
Also known as a cumulative distribution function, an ogive line is used in statistics to show the distribution of results across a range. In the case of test scores, for example, the line would show how students scores are distributed along the possible results. Excel 2013 does not have a specific ogive function, but you can use the Histogram tool to show distributions, add a cumulative percentage line to the graph, and then delete the bars in the histogram to leave only the ogive. Before getting to the data, load the free Analysis ToolPak to add extra analysis tools to Excel.
Load the Analysis Toolpak
Open Excel, click "File," and then select "Options."
Click "Add-Ins," click "Excel Add-Ins" in the Manage box, and then click "Go."
Check the box next to Analysis ToolPak in the Add-Ins Available box, and then click "OK" to load extra analysis tools into Excel.
Create an Ogive
Open a new workbook.
Label cell A1 with a title for your data, such as "Test Scores" or "Income."
Enter all of your data into column A. Each cell should contain one numerical value. Continue all the way down until you've listed all of your data for the ogive.
Label cell B1 "Bins." Bin numbers are the intervals measured by the histogram. In the case of test scores, for instance, these numbers could be from zero to 100, in intervals of 10 to capture all of the results.
List all of your bin numbers in ascending order in column B. The smaller your intervals, the more precise your histogram or ogive will be. For test scores, you could list all numbers from zero to 100, although more complex data like sample incomes may require larger ranges. Bin numbers will appear on the histogram as values on the horizontal axis.
Click "Data," and then select "Data Analysis."
Click "Histogram," and then click the "OK" button to load the histogram configuration options.
Click in the Input Range field under "Input," and then click cell A1. Hold down the mouse button and move down to highlight all of your data in column A. The cell range for your data appears in the Input Range box.
Click in the Bin Range field under "Input," and then click cell B1. Hold down the mouse button to highlight all of your bin values in column B. The appropriate cell range appears in the Bin Range field.
Check the "Labels" box to include the column headings as axis titles in your histogram.
Choose whether you want the histogram to appear in the same worksheet as your data or in a new sheet or workbook under Output Options.
Check the box next to "Cumulative Percentage" to add a cumulative distribution line, or an ogive, to your histogram.
Click "OK" to create a histogram with a cumulative distribution line.
Tips & Warnings
- Excel lets you modify any element of a chart. If you only want the ogive without the distribution bars from the histogram, click on each of the bars and press the "Delete" key. The line and axes will remain in your chart.