## Simulated Probability Chart Example.Microsoft Excel does not offer a built in capability to chart probability data, but the technique described here allows you to simulate a probability scale along a chart axis (the vertical axis in this example). This is related to the This probability chart is an application of the Arbitrary Axis Scale and Arbitrary Gridlines examples shown elsewhere on this website. Another implementation of these techniques is shown in the Reciprocal Axis Chart. I started with the following sample data in A1:C8 of my worksheet.
=NORMSINV((B2-0.5)/MAX($B$2:$B$8)) in cell C2 and dragging it down to fill C2:C8. I added the following data in E1:G6 to provide a dummy series for my probability scale Y axis.
Under =NORMSINV(E2) and the formula is dragged down to fill the values in G2:G6. More labels can be created if desired. For unlabeled tick marks and gridlines, I added the following in I1:K9 of the worksheet.
=NORMSINV(I2) and drag it down to fill K2:K9. Now we can start constructing the chart.
I constructed Chart 1 with my dummy axis series, plotting In Chart 1a I added the series containing my real data, In Chart 2 I have added labels from the I formatted the Y axis in Chart 3. In the Patterns tab of the Format Axis dialog box, I set the Major Ticks, Minor Ticks, and Tick Labels all to In Chart 4 I have added error bars to my dummy axis labels to serve as horizontal gridlines (blue for clarity). Select the series, press Ctrl-1 to open the Format Series dialog, click on the X Error Bars tab, and enter 8 in the Custom + box (in this example, 8 is the width from the minimum to the maximum on the X axis). I formatted these error bars (select the error bars, press Ctrl-1) as hairline width, without the cross ("T") at the ends. I also scaled the Y axis to range between -2.5 and +2.5. In Chart 5 I have added smaller crosses (size 5) for the minor probability tick marks. I removed the legend entry for this dummy series, and added hairline error bars (red) for minor horizontal gridlines, again without the crosses at the ends. I used the dashed line style just for fun. The final steps are shown below. I changed the dummy axis tick and label colors to black, and the dummy major and minor gridlines to gray (gray competes less with the actual plotted data than black). I changed the legend entry for the test data series, and moved the legend. And I added a descriptive chart title. To downoad a zipped Excel file with this example, click on this link. |

## Peltier Technical Services, Inc.## Excel Chart Add-Ins | Training | Charts and Tutorials | PTS BlogPeltier Technical Services, Inc., Copyright © 2017. All rights reserved. |