How Do I Make an Ogive in Excel?

How Do I Make an Ogive in Excel
How Do I Make an Ogive in Excel

How Do I Make an Ogive in Excel? If you need to display and analyze data on a regular basis, learning how to build an ogive in Excel is a vital skill to have since it enables you to exhibit cumulative frequencies using the Excel spreadsheet software that comes with with Microsoft Office. The construction of the ogive chart is an easy technique that entails doing only a few elementary calculations prior to charting the graph. Excel is a useful tool for creating ogive graphs due to the built-in computation and graphing features that are included in the program.

What Exactly Is an Ogive Chart ?

Displaying the cumulative relative frequency along the y-axis among a number of groups that are organized along the x-axis in the form of a line graph is what an ogive chart does. The term “cumulative relative frequency” is the one that’s most important. The number of occurrences of something is referred to as its “frequency.” For example, if you have a group of twenty students and five of them wear shoes with a size 8 heel, then the frequency of people in the group who have feet of that size is five. Changing this into a relative frequency would require scaling it to the size of the overall group; for example, if there are 20 students total, then five of them would make up a quarter of the group, which would be equal to 25 percent.

The term “cumulative” refers to the fact that the frequency displayed for each group takes into account not only the frequency of the current group but also the frequency of each group that came before it. Therefore, if five students have feet that are a size 8, but two have feet that are a size 7, two have feet that are a size 6, and one has feet that are a size 5, then the “size 8” section displays 10, which is 50 percent, and shows that half of all students have feet that are a size eight or smaller.

A simplified explanation of what an ogive chart is would be to describe it as a line graph that rises from one group to the next in proportion to how much contribution each group makes to the overall relative frequency. By the time we get to the final group, the cumulative relative frequency will equal one (in decimal form), which is equivalent to one hundred percent.

Getting Ready to Present Your Data

Creating an ogive chart in Excel is actually the same as creating a cumulative frequency graph in Excel; hence, the data needs to be organized in the appropriate manner. Imagine that you are working with the data on shoe sizes that were described in the prior section:

Size 5: one

Size 6: two

Size 7: two

Size 8: five

Size 9: six

Size 10: three

Size 11: one

One of the columns in Excel should be used to display the sizes, and the next column should be used to display the number of students included in each group (the frequency). Make a new column and label it “Cumulative Frequency.” Then, to get your running total, add the values from the previous column to the new column’s values. Utilizing Excel’s “Sum” function is the quickest and easiest approach to complete this task. If your data runs from cell B2 to cell B8, type “=SUM($B$2:B2)” in cell C2 and then drag the formula down to cell C8 by clicking and dragging the bottom right corner of cell C2 downward. The formula has to be updated so that it calculates the total from cell B2 to the cell that is immediately adjacent to the cell that contains the formula. If you have done everything correctly, the total number of observations should be in the very last cell. If we were talking about shoe sizes, this would be a size 20.

Include a column to track the cumulative relative frequencies, then perform the necessary calculations. To accomplish this, divide each of the cumulative frequencies by the sum that was found in the row below it. In the demonstration, you give the cell the formula “=C2/20” and then move it along the column. Instead of typing “=C2/20*100,” you should provide the percentages you want.

Excel procedure for creating an ogive

Excel now allows you to construct an ogive using your data if you want to. After highlighting the data, select the “Insert” tab to continue. Select “Line with Markers” from the option that appears when you click the down arrow next to “Line.” When the columns aren’t adjacent to each other, it’s more difficult to highlight the data. However, you can still highlight everything and then eliminate the extra series that appear based on the frequency and cumulative frequency columns. This is the chart of the ogive. From this point forward, you will be able to alter the graph or the category labels to better suit your tastes or requirements.