Y axis, need varying units

N

NTE

I'm using Excel 2002.

I have data that looks like this:
X Y
2.56937391 4.077622647
2.58546073 8.180775241
2.589949601 12.29105172
2.657055853 16.50782651
2.741151599 20.8580621
2.854306042 25.38787495
2.862131379 29.9301067
2.935507266 34.58878672
3.192009593 39.65453856
3.250175948 44.812601
3.266936911 49.99726325
3.268343914 55.18415844
3.339451441 60.483902
3.354684554 65.80782066
3.454234896 71.28972673
3.465234095 76.78908865
3.532754379 82.39560594
3.572755465 88.06560537
3.582631439 93.75127807
3.937417581 100

I need to produce a scatter plot in excel. Simple to do in general.
However, the requestor has asked me to have the major y-axis values be these
(evenly spaced on the y axis):

0.1
1
5
20
50
80
95
99
99.9

I was able, using information found at http://PeltierTech.com/ (thank you
Jon) to make a dummy series that would portray the axis as I needed it.
However, the data is still plotted on the "real" axis and obviously does not
line up with the "fake" y-axis values. Because there is a different amount
of "space" between the major ticks, the distance between points will need to
vary.

Any suggestions appreciated. I am going to try a SAS solution as well, but
the end-users prefer to be able to do it in excel.
 
N

NTE

No. My numbers are all above 1. Basically what I've been asked to do is
visually "squash" the datapoints.

It is kind of like a log-scale, in that we want to show more information in
less space, but it is not a probability plot. The x variable is a log of a
workplace protection factor for certain equipment. The y variable is the
cumulative percent.

NTE
 
J

John Mansfield

NTE,

Try setting your data up like this . . .

X Y X Y
0.00000 0.00000 2.5 0.1
0.00000 0.00000 2.5 1.0
2.56937 4.07762 2.5 1.0
2.58546 8.18078 2.5 5.0
2.58995 12.29105 2.5 5.0
2.65706 16.50783 2.5 5.0
2.74115 20.85806 2.5 20.0
2.85431 25.38787 2.5 20.0
2.86213 29.93011 2.5 20.0
2.93551 34.58879 2.5 20.0
3.19201 39.65454 2.5 20.0
3.25018 44.81260 2.5 20.0
3.26694 49.99726 2.5 20.0
3.26834 55.18416 2.5 50.0
3.33945 60.48390 2.5 50.0
3.35468 65.80782 2.5 50.0
3.45423 71.28973 2.5 50.0
3.46523 76.78909 2.5 50.0
3.53275 82.39561 2.5 80.0
3.57276 88.06561 2.5 80.0
3.58263 93.75128 2.5 80.0
0.00000 0.000000 2.5 95.0
0.00000 0.000000 2.5 99.9
3.93742 100.0000 2.5 99.0

The second set of X and Y values act as a dummy Y axis. All X values are at
2.5 because that's where we want the Y-Axis to be located. All of the Y
values act as a range in which your original Y values would fall. Add zeros
where there are values within the desired Y axis where no "real" Y axis
values fall.

To build the chart, perform the following steps . . .

(1) Create an X-Y Scatter Chart based on the first X-Y set with "Series in"
set to columns.
(2) Double-click on the X-Axis and set the minimum X-Axis scale to 2.5.
(3) Go to "Source Data" and add a second series called "Dummy Y" based on
the second X-Y set.
(4) You should see points for the new "Dummy Y" series running up the
Y-Axis at X = 2.5.
(5) Double-Click on the true Y-Axis labels and under the Patterns option,
set all tick marks to "None".
(6) Go to Chart -> Chart Options and uncheck all Gridlines (turn them off).
(7) Double-click on any of the Dummy Y-Axis points.
(8) Turn the Data labels for the Y-value on.
(9) Go to the X-Error Bars tab, select Plus, error amount option = fixed,
value = 2.6.
(10) Double-click on the X-Axis and set the maximum X-Axis scale to
approximately 4.1.
(11) Double-click on any of the Dummy Y-Axis points and set the markers to
"None".
(12) Activate the legend and delete the "Dummy Y" entry (click on it once
and hit the delete key).
(13) Align Dummy Y-Axis labels to the left side of the chart.
(14) May need to adjust the upper scale of the chart if needed.
(15) Perform any other formatting changes desired.
 
J

Jon Peltier

You meant to say Yes. Your numbers are between 0 and 100. I assume this is 0 and
100%, in fact, you say they are cumulative percentages. The Y axis numbers you've
indicated are commonly used in probability plots, but they are not, strictly
speaking, evenly spaced.

Barb suggested this web page:

http://peltiertech.com/Excel/Charts/ProbabilityChart.html

I suggest you go to this page, and see what a cumulative probability plot looks
like, then decide whether in fact, the way that it shows a cumulative percentage of
a factor plotted on the X axis is what you need.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
N

NTE

John,
thank you for taking the time to put together that example. I followed your
directions. However, unless misunderstood something you said, the graph I
got was still not what I was looking for. The y-axis values are not evenly
spaced. I need those y axis tics to be evenly spaced (as if there was an
equal distance between them, even though there is not) and the points
themselves should "move" to the appropriate place. Someone I worked with has
mentioned that I need to investigate a transformation for the y-axis.....I'm
pretty sure excel can't do what we need. I have gotten SAS to almost do it.
I appreciate your time.
NTE
 
N

NTE

Jon,

I went back and re-read the
http://peltiertech.com/Excel/Charts/ProbabilityChart.html information.

I then tried to format my data as suggested in the example. So , it looked
like this (my apologies for the wrapping):

label Xlabel Ylabel Xdata Rank Yrank
0.040776226 0 -1.741748078 2.56937391 1 -1.937930375
0.081807752 0 -1.393014526 2.58546073 2 -1.412187915
0.122910517 0 -1.160559893 2.589949601 3 -1.118958596
0.165078265 0 -0.973798618 2.657055853 4 -0.899434781
0.208580621 0 -0.811355814 2.741151599 5 -0.71649726
0.25387875 0 -0.662333303 2.854306042 6 -0.554922843
0.299301067 0 -0.52641172 2.862131379 7 -0.406724367
0.345887867 0 -0.396446536 2.935507266 8 -0.266994277
0.396545386 0 -0.262299357 3.192009593 9 -0.132312766
0.44812601 0 -0.130397314 3.250175948 10 5.47142E-10
0.499972633 0 -6.86002E-05 3.266936911 11 0.132312766
0.551841584 0 0.130315392 3.268343914 12 0.266994277
0.60483902 0 0.265892704 3.339451441 13 0.406724367
0.658078207 0 0.407223963 3.354684554 14 0.554922843
0.712897267 0 0.561868613 3.454234896 15 0.71649726
0.767890886 0 0.7319184 3.465234095 16 0.899434781
0.823956059 0 0.930547032 3.532754379 17 1.118958596
0.880656054 0 1.178273031 3.572755465 18 1.412187915
0.937512781 0 1.534224617 3.582631439 19 1.937930375
3.937417581 20
And yes, I did get a nice looking graph. However, as you noted, the
major"ticks" on the dummy axis are not evenly spaced. Plus, I wanted only 9
y axis values, specifically
0.1
1
5
20
50
80
95
99
99.9

but the way this worked out I got 19 y axis values, and of course they were
not set to the 9 values I wanted.
Thank you for help, and the probablilty chart method will come in helpful
for other applications.
NTE
 
J

Jon Peltier

I should have more clearly noted that the data for the dummy label series, in the
first three columns, is independent of the data for the actual data points. In my
example I had as many labels as data points, and the ranges therefore looked
connected. You don't want labels at 0.040776226, 0.081807752, etc.

Your label data should be (change the zeros in the second column to whatever the X
axis minimum is):

label Xlabel Ylabel
0.1% 0 -3.090244718
1% 0 -2.326341928
5% 0 -1.644853
20% 0 -0.841621386
50% 0 0
80% 0 0.841621386
95% 0 1.644853
99% 0 2.326341928
99.9% 0 3.090244718

Construct your dummy axis with these points and labels.

For your regular values, if the numbers that seem so much like percentages are
percentages, you turn them into Y values with the same NORMSINV function. If I put
the X values and the Cum% values into columns E and F, with headers in E1:F1 and
values starting in row 2, cell G2 should have this formula:

=NORMSINV(F2)

so your regular data should look like:

X Cum% Y
2.56937391 4.077622647 -1.741746019
2.58546073 8.180775241 -1.393013918
2.589949601 12.29105172 -1.160560714
2.657055853 16.50782651 -0.973798251
2.741151599 20.8580621 -0.811355676
2.854306042 25.38787495 -0.662332695
2.862131379 29.9301067 -0.526410986
2.935507266 34.58878672 -0.396446467
3.192009593 39.65453856 -0.262299409
3.250175948 44.812601 -0.130397666
3.266936911 49.99726325 -6.82121E-05
3.268343914 55.18415844 0.130314675
3.339451441 60.483902 0.26589305
3.354684554 65.80782066 0.407223979
3.454234896 71.28972673 0.561868774
3.465234095 76.78908865 0.731918135
3.532754379 82.39560594 0.93054723
3.572755465 88.06560537 1.178273124
3.582631439 93.75127807 1.534226612
3.937417581 100 #NUM!

The last value gives an error, because for a normal distribution, you never really
get to 100%. So ignore it. Judging from the first column, you should probably change
the X axis minimum to 2.5 and maximum to 4, and change the column of Xlabel values
from zero to 2.5.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
N

NTE

Thank you Jon. I followed your example. Plus added in your tricks with
using the error bars as gridlines, etc. I now have a graph that approximates
what I was asked to produce. I will never again doubt the ability of excel
to produce a visually correct complicated graph.
NTE
 
Top