If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
You just have to figure out how to tell Excel what to graph.
- Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ NTE wrote: 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 "Jon Peltier" wrote: 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/ _______ NTE wrote: Jon, I went back and re-read the http://peltiertech.com/Excel/Charts/...lityChart.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 "Jon Peltier" wrote: 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/...lityChart.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/ _______ NTE wrote: 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 "Barb Reinhardt" wrote: Are you trying to make a probability plot? "NTE" wrote in message ... 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. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Problem with the display of a 2 axis chart | Peter B | Charts and Charting | 1 | August 4th, 2004 02:39 AM |
Different units on same axis in diagram (for example Nm and Lbinon X and MPa and PSI on Y) | Jon Peltier | Charts and Charting | 0 | April 30th, 2004 05:57 AM |
force graph to axis of choice | Tushar Mehta | Charts and Charting | 3 | February 11th, 2004 04:04 PM |
Grams to Pounds | Grams to Pounds | Worksheet Functions | 10 | December 12th, 2003 07:32 PM |