How to Create a Probability Distribution Graph in Excel

Open Microsoft Excel. The app has a white “X” icon on a green background. The Excel spreadsheet collection page opens.

• On Mac, this step can open a new Excel sheet with no data. Then skip to the next step.

Create a new document. Click Blank workbook in the upper-left corner of the window (Windows), or click File and select New Workbook (Mac).

Determine the smallest and largest data point. This is quite important in determining the counts in each drawer and the number of compartments required.

• For example, if your data range spans 17 to 225, the smallest data point will be 17 and the largest 225.

Determine the number of drawers you need. Boxes are used to organize data into groups on a probability distribution chart. The easiest way to calculate the number of bins is to take the largest data point (in the example, 225), divide it by the number of data points in the graph (for example, 10) and then round up or down to the nearest integer, however rarely do we have more than 20 or less than 10 numbers. You can use the formula if you are not familiar with: ^[1]

• Sturge’s formula : K = 1 + 3.322 * log(N) where K is the number of drawers and N is the number of data points; after you find K, round up or down to an approximate integer. Sturge’s formula is best suited for linear or “clean” datasets.
• Rice’s formula : cube root of (number of data points) * 2 (for a dataset with 200 points, you’ll need to find the square root of 200, then multiply the result by 2). This formula is best suited for erratic or inconsistent data.

Determine the counts in each drawer. Now that the number of bins is known, you can find the most uniform distribution. The counts in each bin consist of the largest and smallest data points, which will increase in a linear fashion.

• For example, if you were to generate data for each box of a probability distribution representing test scores, you would most likely use the increment operator of 1 to represent different scales (e.g. 5, 6) , 7, 8, 9).
• Increasing multiples of 10, 20, or even 100 is a commonly used standard for the number of counts in each drawer.
• If there are outliers that mutate, you can either put them outside the range of cells or increase/decrease the range of counts in each bin enough to include the outlier value.

Add data to the column. Enter each data point in its own cell in column A .

• For example, if you have 40 data, you can add each corresponding number to cells A1 through A40 .

Add the counts in each drawer to column C if you use a Mac. Starting in cell C1 and below, enter each number in each box into the cell. After completing this step, you can proceed to create the probability distribution graph.

• Skip this step on a Windows computer.