Knowing how to calculate relative and cumulative distribution in Excel can be useful in a variety of circumstances. It can be used in market analysis to sort a sample of people into age groups. It can also be used in sales to show monthly sales figures. Relative distribution shows the proportion of each group as a percentage of the whole. Cumulative distribution adds each group to those below it. For example, cumulative distribution can show how many people in a group are below the age of 20, compared to the number below age 40. In sales, it could show cumulative sales up to a current month.

Populating the Worksheet

1. Launch Microsoft Excel and open a new workbook by pressing "Ctrl" and "N" simultaneously on the keyboard. This example will use a room of 26 people, showing age groups to illustrate the relative and cumulative distribution of each age group in the room.

3. Type "20" in cell D2, "30" in D3, "40" in D4 and "50" in D5. These numbers represent age groups for the group of people in the room.

4. Type "8" in cell E2, "9" in E3, "7" in E4, and "2" in cell E5. These values represent the Distribution of these age groups in the room.

5. Click cell "E6." Click the "AutoSum" button and then press the "Enter" key. This adds the values in the column to show that there are 26 people in the room.

Relative Distribution

1. Type "=E2/$E$6" in cell F2. This function divides the first Distribution value by the total number of people in the room. The "$" anchors cell E6 so it remains constant when you copy this function to the other cells in this column.

2. Move the cursor to the bottom right corner of cell F2. The cursor changes to a "+" symbol. Drag the mouse down from cell F2 to F5 to copy the formula into the cells of this column.

3. Click on a cell in this column to see that the formula has been automatically adjusted. Each cell uses the Distribution value beside it and divides this by the total in cell E6 to reveal the relative distribution.

Cumulative Distribution

1. Click in cell "G2." Type "=SUM($E$2:E2)" in the cell. For the first value in cell G2, the number will be 8, the same as in E2.

2. Move the cursor to the lower right corner of cell G2 until it changes to a "+" symbol. Drag the cursor down to cell G5 to fill the cells in this column with the formula.

3. Observe that each cell in this column now reveals the cumulative distribution of each age group. The total number of people aged 20 or below is 8; the total below age 30 is 17. The total number of people below age 40 is 24, and the total below age 50 is 26, which is the same as the total number of people in the room.

About the Author

A published author and professional speaker, David Weedmark has advised businesses and governments on technology, media and marketing for more than 20 years. He has taught computer science at Algonquin College, has started three successful businesses, and has written hundreds of articles for newspapers and magazines throughout Canada and the United States.