How to Make a Probability Versus Data Point in Excel

by C. Taylor

Probability calculations determine the chance of a specific event occurring. This calculation factors in the number of positive events with respect to the total number of possible results. When you have a list of data observations, applying probability analysis estimates the chances of an observed data point recurring. In Microsoft Excel, using a scatter chart allows you to plot the data points against the probability of achieving each observed data point.

1. Open Microsoft Excel and list your observed data points in column A.

2. Enter “=COUNTIF(A:A,A1)/COUNTA(A:A)” without quotes in cell B1.

3. Click and drag the lower right corner of cell B1 down to the last cell that corresponds to data in column A. This copies the formula to all the selected cells. As an example, if you had data in cells A1 through A100, drag the lower right corner of B1 down to B100 to copy the formula to all 100 cells. Cells B1 through B100 remain selected.

4. Hold the “Ctrl” key and press “C” to copy all the selected cells. Click “Paste” from the Home tab’s Clipboard group and select “Values Only." This option is the first one under the Paste Values section. Doing this removes the formulas but keeps the calculated values.

5. Click cell A1. Hold the "Shift" key and click the last cell with data in column B to select all data. Click the "Data" tab and click "Remove Duplicates" from the Data Tools section. Uncheck "Column B" from the Remove Duplicates window and click "OK." This removes all duplicate observations, but leaves their respective probabilities.

6. Select all remaining data, just as you did in the previous step.

7. Click the “Insert” tab. Click “Scatter” from the Charts group and select the type of scatter chart you prefer. You can choose to use only points, points and lines or just lines. The chart is automatically created to plot data points versus probability.

Photo Credits

  • Hemera Technologies/ Images
Suggest an Article Correction