# How Do I Make an Ogive in Excel?

Learn how to make an ogive curve in a Line or Scatter chart format using the Excel FREQUENCY function.

### Introduction

An ogive (pronounced as "oh-jive") is a graphical representation of a cumulative frequency distribution and can be created using Microsoft Excel. However, the data on which the ogive is based needs to be organized in a certain way so that the required values are available. Typically, the data is placed into a frequency distribution, like the one shown in the following example. If you understand frequency distribution tables and cumulative frequency values, you may skip to Step 3. However, if all of this is new to you, continue on to Step 1.

#### Step 1

The =FREQUENCY() function tallies the number of data values that fall into specified class ranges. For instance, from the data in the sample, this function produces a tally of the Data values for each of the classes, defined by the upper limits of the Bins column.

Excel's FREQUENCY() function uses two arguments: the cell range of the data values and the cell range of the class upper limits. Enter your raw data in one column and the upper limit of each class range in another.

#### Step 2

On the Excel worksheet, highlight only the cells containing data.

On the Home tab of the Excel Ribbon, click on the **Sort & Filter** icon and choose **Sort Smallest to Largest** on its menu to sort the data into ascending order.

#### Step 3

Select the cells in which you want to place the frequency counts; cells C2 through C8 are used in the example. Click onto the formula bar and enter the **=FREQUENCY(data_range,bins_range)** function with the cell range for the data replacing **data_range** and the **cell range** for the class upper limits as the bins_range. Using the data in the examples, the entry on the formula bar is **=FREQUENCY(A2:A31,B2:B8)**. The data_range is A2:A31 and the bins_range is B2:B8. Do NOT press Enter or click the check mark yet!

Press the **Control, Shift, and Enter** keys simultaneously (**CTRL+SHIFT+ENTER**) to apply the function. Braces ({ }) are placed in front and behind of the function statement to indicate that it is an array formula. The frequency distribution counts are placed in a cell associated with each upper limit, as shown in the previous example.

#### Step 4

#### Tip

The examples given here are relatively simple. To use a function to add the cumulative count down the distribution classes, you can use **=SUM()** progressively or some version of adding the value of the cell above (0 to the first cell) to the frequency value on that row.

The last step in preparing the data for an ogive curve is to add up the cumulative distribution values. These values are a running total that starts with the first class and ends with the last class. The example shows the cumulative distribution on the example worksheet.

#### Step 5

Select the **Insert** tab on the Excel ribbon and choose the **Line** chart or **Scatter** chart option in the Charts section. The two most commonly used chart types are the Marked Line (on the Line chart menu) and the Scatter with Lines and Markers (on the Scatter chart menu).

Select the cumulative frequency values, then choose the chart type for the ogive. You should now have an ogive curve.