Calculate Cumulative Percentage – Excel and Google Sheets
This tutorial demonstrates how to calculate cumulative percentages in Excel and Google Sheets.
A frequency distribution is a distribution of the number of occurrences of a set of events. In other words, a frequency distribution shows different values in a dataset and the number of times the values occur in the dataset. A cumulative frequency distribution shows the total number of occurrences of the events not higher (or not lower) than a particular event in a given set of events. In other words, a cumulative frequency distribution shows the number of observations that lie from a value and above (or below) the value in a dataset.
The cumulative percentage shows the percentage of the number of occurrences of an event and the events below it compared to the total number of all events. In other words, a cumulative frequency distribution shows the proportion of observations in a dataset that are equal to or less than a specified value, indicating either no higher or no lower positioning.
The cumulative percentage is calculated by dividing the cumulative frequency by the total frequency. That is, by dividing the sum of the frequency of an event and the frequencies before it (or after it) by the total frequency.
Cumulative Percentage Formula
The cumulative percentage is calculated using the formula:
where fi+cfi-1 is the cumulative frequency of each event, value, or class;
fi is the number of occurrence (frequency) of the event, value, or class;
cfi-1 is the cumulative frequency of the preceding event, value, or class; and
Σf is the total frequency.
How to Calculate Cumulative Percentage in Excel
Background: An internet service provider (ISP) is conducting a customer satisfaction survey from a random sample of its users. They asked each surveyed customer to rate their services in the past 90 days on a scale of 1 to 10, where 10 represents “excellent” and 1 represents “miserable.” The results from the survey are collated and presented in the table below. Calculate the cumulative percentage of the result of the survey.
- First, calculate the cumulative frequency distribution of the scores by adding each frequency to the preceding frequencies.
Note: The last value of the Cumulative Frequency column must be equal to the sum of all frequencies. That is, the last value of the cumulative frequency distribution is equivalent to the total frequency.
- Next, calculate the cumulative percentage by dividing each cumulative frequency by the total frequency (the last value of the Cumulative Frequency column).
Note: Double-click the bottom-right corner of the cell to fill down the data to the rest of the column.
The complete Cumulative Percentage column is shown below.
- Next, convert the cumulative percentage values to percentages by selecting the Cumulative Percentage column and clicking the Percent Style icon or clicking CTRL + SHIFT + %.
- The Cumulative Percentage graph looks exactly like the Cumulative Frequency Distribution graph, except for the change in the vertical axis labels.
To visualize the cumulative percentage graph, create a cumulative percentage curve (ogive).
Hold down the CTRL key and select the Satisfaction Score and Cumulative Percentage columns. - Then in the Ribbon, go to Insert > Charts > Insert Scatter (X, Y) or Bubble Chart > Scatter with Smooth Lines and Markers.
The resulting scatter (ogive) chart is shown below.
- Edit the chart title by clicking on the Chart Title text in the chart and typing your desired title.
- Add the horizontal and the vertical axis titles. Select the chart, and then in the Ribbon, go to Chart Design > Add Chart Element > Axis Title > Primary Horizontal for the horizontal axis title. Repeat for the vertical axis title with Primary Vertical.
- Then, edit the titles to accurately describe your data.
Cumulative Percentages in Google Sheets
Cumulative percentages can be calculated in Google Sheets in a similar way.
- First, follow the method described in the Excel section to obtain the Cumulative Percentage table.
- To create the cumulative percentage curve (ogive) in Google Sheet, first, highlight the Satisfaction Score and Cumulative Percentage columns, and then click the Chart option from the Insert tab.
- On the Chart editor, change the Chart type to Smooth line chart in the Setup option.
The resulting graph is shown below.
- To add the data point markers, first click Customize. Then under Series, click Add in the Format data point section.
- In the pop-up dialog box, get the Data point from the chart or drop down and click OK.
- Set the Color, Point size, and Point shape.
- Repeat the above process until you have added all the data points.
The resulting chart is shown below.
Tip: An easier way to add the data point markers is to check the Error bars checkbox in the Format data point section and then set the Value to 0.
