Creating an Excel log-probability chart?
Ah, but Tushar, you forget your own technique, which Larry can use to
make a probability scale axis. Just like the log scale with arbitrary
endpoints on your site.
Just to show Larry how it's done (or visit Tushar's web site,
Start with an XY Scatter chart showing some values on the X axis and
their cumulative probability distribution on the Y axis (actually
NORMSINV of the cumulative percentage). Put this data into a worksheet
1% 0 -2.326341928
5% 0 -1.644853
10% 0 -1.281550794
20% 0 -0.841621386
30% 0 -0.524401003
50% 0 0
70% 0 0.524401003
80% 0 0.841621386
90% 0 1.281550794
95% 0 1.644853
99% 0 2.326341928
where the first column is the cumulative normal distribution points
where you want markers, the second column is all zeros (or whatever the
X axis minimum value is), and the third column lists the NORMSINV values
from the first column. If this range is A1:C11, put this in cell C1 and
drag it down:
Make a new series using the second column for X values and the third for
Y. Format the markers as black crosses, maybe size 6 or 7 (to look like
axis tick marks). Format the "real" Y axis, on the patterns tab, so
there are no major or minor tick marks and no tick labels. The left
edge of the plot area jumps to the left side of the chart area, so
select the plot area and shrink its left side to make more room.
For the next part, download Rob Bovey's XY Chart Labeler, a free addin
from http://www.appspro.com. Install it; there is a new entry at the
bottom of the Tools menu, XY Chart Labels. Use this entry to add data
labels to the series you just added above, along the Y axis. The range
containing the labels is the first column, 1% on down to 99%, and put
the labels to the left of the points. Presto, instant probability scale
axis. You can still select and format the hidden Y axis; for example,
you might like to scale it from -2.5 to +2.5.
A final trick if you want gridlines. Select the dummy series used to
mark the Y axis, right click and select Format, go to X error bars.
Select "Plus", and enter a value in the Fixed Value box that extends
beyond the plotted data to the X axis max. You need to rescale the X
axis max and min now, because Excel likes to add empty space beyond the
last plotted element in a chart.
> I believe, though I'm not a 100% sure, that you want Mike Middleton's
> site. Specifically, the page you want is
> http://www.usfca.edu/~middleton/data.htm and the link within the page is
> 'Normal Probability Plots Using Excel (5-page 43KB PDF)' Hopefully, you
> will be able to adapt his ideas for your needs.
> Tushar Mehta
> > I want to create a log-probability chart with Excel 97. Along the
> > logarithmic x-axis are values ranging from 0.1 to 1000. The y-axis is
> > probability, P1 (probability at 1% chance) almost at the top of the
> > y-axis and P99 (probability at 99% chance) near the bottom. Being
> > probability the y-axis is not linear nor logarithmic. The P40-P70
> > lines are much closer together than the other end point of the
> > probability axis (a log-normal distribution of x-values plots as a
> > straight line in this style of graph).
> > I already have my P1, P10, P50, P90 and P99 values calculated but my
> > question is how to construct the y axis properly usnig Excel. Any
> > ideas out there would be greatly appreciated.
> > Thanks...Larry Gagnon