How to Get Relative & Cumulative Distribution in Excel

Relative frequency provides a ratio of the number of times something happens in a given categorical list compared to the total number of times that same thing happens. For example, the ratio can identify the frequency of sales of a product relative to the total number of sales. Cumulative distributions give a running total of frequencies in an ordered list. For example, you might want to see the cumulative frequencies of sales of various amounts. Both of these distributions are simple to create in Excel.

Relative Frequency Distributions

Enter the name of the item for which you want to create a relative frequency distribution in cell A1; for example, “Product Name.” Enter “Frequency” in cell B1 and “Relative Frequency” in cell C1.

List all your items starting from cell A2 and working downwards. These can be in whatever order makes the most sense to you.

Enter the volume of sales (or whatever variable you're interested in) in the B column, in the cell directly to the right of the relevant item.

Type the following formula in the cell directly below the lowest value in the B column to sum all the values you just entered. So if you have 10 product items, the lowest of these will be B11 and the formula will go in B12; but replace “B11” in the example with the actual reference of the lowest value in column B:

\=SUM(B2:B11)

Enter the following formula into cell C2, replacing the example reference “$B$11” with the reference of the cell into which you just entered the SUM formula (but keep the dollar signs in place):

\=B2/$B$11

Click cell C2 and then drag the small black box in the lower-right corner of the cell to the bottom of your list. This is the relative frequency distribution.

Cumulative Distribution

Enter the name of the variable for which you want to see cumulative data in cell A1; for example, “Sale Value.” Work from cell A2 downwards, adding the ranges for which you want to calculate frequencies, for example you might move down in multiple of 50 dollars: “$0-$50,” “$50-$100,” “$100-$150” and so on, until you reach the maximum range necessary for your data.

Type “Frequency” into cell B1.

Scroll down your list and add in the relevant frequencies for each of the ranges you have entered.

Enter “Cumulative Frequency” into cell C1 and Type “=B2” (without quotations) into cell C2.

In cell C3 enter “=B3+C2” (without quotations).

Click in cell B3 and then drag the small black square to the bottom of your list. This copies the formula to all cells.