|
|
|
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 Flexible Log Axis Scale technique described by Tushar Mehta on his web site, http://www.tushar-mehta.com. A dummy series is plotted along the Y axis, and formatted to look like an axis, with tick marks and labels. These labels are applied to the dummy axis series using the XY Chart Labeler, a free add-in written by Rob Bovey and available at http://www.appspro.com. 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.
XData Rank YRank
0.5 1 -1.46523
2 2 -0.79164
3.25 3 -0.36611
4 4 0
4.75 5 0.366106
6 6 0.791638
7.5 7 1.465232
XData (Column A) contains the actual data to plot, sorted in ascending order, and Rank (Column B) is the rank order of the XData values. YRank (Column C) is the vertical coordinate corresponding to XData, computed using the formula: =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.
Label XLabel YLabel
0.01 0 -2.32634
0.10 0 -1.28155
0.50 0 0
0.90 0 1.281551
0.99 0 2.326342
Under Label (Column E) are the probability values along my Y axis at which I want to apply labels, tick marks, and gridlines. XLabel (Column F) is full of zeros because I want the corresponding tick marks along the axis, where X = 0 (if your X axis has different scale parameters, you need to use the X axis minimum here). YLabel (Column G) contains the vertical coordinates of the tick marks for these labels. The first Y coordinate in cell G2 is computed using the formula: =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.
Unlabeled Xunlabel Yunlabel
0.05 0 -1.64485
0.2 0 -0.84162
0.3 0 -0.5244
0.4 0 -0.25335
0.6 0 0.253347
0.7 0 0.524401
0.8 0 0.841621
0.95 0 1.644853
Unlabeled (Column I) contains the probability values at which I want tick marks without labels. XUnlabel (Column J) is full of zeros for the same reason XLabel above is full of zeros. YUnlabel (Column L) is computed from Unlabeled as YLabel is computed above: in cell K2 enter this formula: =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 XLabel vs. YLabel.
In Chart 1a I added the series containing my real data, XData vs. YRank. I formatted the X axis with the following scale parameters: Minimum = 0, Maximum = 8, Spacing = 1. These settings depend of course on the data being plotted.
In Chart 2 I have added labels from the Label column of the worksheet (0.01, 0.10, etc. from E2:E6) to the left side of my dummy YLabel series. You can do this by adding data labels to the series (either category or value labels), then manually changing the labels one by one to contain the desired text. But Rob Bovey's XY Chart Labeler, described above, applies these labels automatically. I also have formatted the YLabel series markers as crosses (which I have kept blue for clarity). Use No Color for the background color of these markers so the axis and other features are not obscured by the markers. A marker size of about 7 looks like major tick marks to me.
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 None so they would not show. Then I had to readjust the size of the plot area to make room for the probability scale labels. In the Scale tab, I set Value (X) axis crosses at to -1000, sufficiently low that it will always appear at the bottom of the chart. I removed the horizontal gridlines, and I removed the legend entry for the YLabel dummy series. Do this by clicking the legend once, then clicking the specific entry once, then pressing delete. Double clicking will bring up the Format Legend dialog box.
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. |
