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,

http://www.tushar-mehta.com):

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

somewhere:

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:

=NORMSINV(A1)

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.

- Jon

_______

Quote:

> 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.

> --

> Regards,

> Tushar Mehta

> www.tushar-mehta.com

> --

> > 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