Hello!
Ihave made a stacked column chart with 3 columns. Y axis is in meters, say 0 to 6000m. Now I need to place a xy scatter plot (scatter with smooth line) on left and right side of each column. The scatter has its own x axis range. Y axis meter range is same (0-6000m) but data points are varied. How can I accomplish the job? I have attached the existing stack plot. Thanks in advance.
The attached chart is one way to look at your data. Please understand I am just playing around and in order to understand how to help you I need to know your goal statement.
Turn Data into Information
Turn Information into Knowledge
Turn Knowledge into Direction
Turn Direction into Leadership
Turn Leadership into Results
Stephen Druley
It's not how quickly you think
But how deeply you think
The quality of thinking is measured
by remoteness to conformance
Stephen Druley
You did not provide any xy data in your file.
You can add extra series and change the chart type to xy-scatter.
If you use the primary axis then you will need to use x value between 0.5 and 1.5 for the first column, 1.5 and 2.5 for the second and so on.
If you need to have normal and log Y values then you will need to either move the series to the secondary axis in order to use a secondary Y log scale.
Or use formula to calculate the log value and plot that on the normal scale.
Thanks Andy. You are really great. Is it possible to make X-axis range for the first curve 0-150 (linear scale? And for the 2nd curve x axis range from 0.2 -2000 in log scale? I am trying for a long time. But failed. Please donate your some time for me. I remain indebted to you.Originally Posted by Andy Pope
You will not be able to have 3 x axis.
Instead you need to use the secondary axis scale and calculate proportioned values for the 2 sets of X values.
Hi Andy
Plz donīt get irritated thinking that you are handling a fool. I am learning; I have interest. In your first reply you said x value between 0.5 to 1.5 for the first and 1.5 to 2.5 for the second etc. How you calculated same in the category axis?
In the second reply you said it is not possible to have 3 axis. When an authority says so I have to accept it. Is it possible to do the same with VBA? I tell you quickly again: I want one curve each on both sides of each column. The x axis range are different for the two curves on each side of a column.
Please guide me which way to go about it. My best regards.
S K ROY
Correction.
Please read `` The x axis range are different for the two curves associated with each column`` instead of ``The x axis range are different for the two curves on each side of a column.``
As i indicated in my previous note, it is less important to share your objectives about the design of the chart but more important to share your goal about the endstate of the data. If you use Andy's expertise to establish the perfect chart, what wouild be the outcome of the chart that would provide the breakthrough you desire.
By articulating the endstate one can back up and create the graphical representation best suited to understand and ultimately achieve this endstate.
You still have not provided an example of the x values associtated with the chart so I can only describe what you need to do.
A chart has 2 axes. Currently the primary axis is being used by the columns. When a xy series is plotted on this axis the left side is 0.5 and each column center is an integer value, i.e. 1,2,3,4,5... up to the number of columns. If you use an xy series with x values greater than the number of columns+0.5 more space will be created for the columns having the effect of squashing them to the left.
So to use the primary axis you will need to recalculate your values to +/-0.5 of a column position. As you have not provided example x value I do not know whether each or all line sets are within your 0-150 range.
If you move the xyseries to the secondary axis you will only have to rescale the log set of data.
Hi Andy
I am sorry. You really asked for the x values in your previous guidance. I will organize and post both set of x values-one for left side of the column having linear scale in the range of 0-150 and another set of x values for the right side of the column having log scale in the range 0.2-2000. I think you have indicated rightly-we can use first set with primary x axis and second set (log scale) with secondary x axis. But even then problem remains and that is how to place curves for other columns! Anyway you need not reply now. Let me first post those 2 sets of x series data in my next. Till then thank you very much.
SDruley is asking me again and again about the purpose. Program is available for what I am trying to do with help from Andy But it is basically my recently developed interest in Excel and I am trying to do same without any large and complex program. Neither everybody has access to those programs. I am basically a geologist and trying to see whether there are simpler ways to do things. Hope this help satisfy SDruleyīs curiosity. Would like to know if he has similar interest. Thanks.
Last edited by S K ROY; 11-06-2011 at 06:45 PM.
Hi Andy
2 sets of x values and associated y values are ready and I have attached. Please have a look.
Thanks.
You have a problem with 1 set of data in that it has more than 32k data points.
So you might want to cut down the number of points used.
The formula recalculates x values in order to use the same axis for columns, x and logx values.
In order to post I have had to reduce the amount of data from your example file. So you will need to copy and paste over valid records for the X values.
Last edited by Andy Pope; 11-07-2011 at 05:43 AM.
Hi Andy
Got it. Just tell me how much data points maximum I can keep. I will do it accordingly and post it for you and others to see. Thanks for giving me so much of your time.
32K data points per series.
Dear Andy
I have sorted data and got the plots. Though it is done using your formulas, I was ruminating how you have done. You said extreme left of x axis is 0.5. But you have not used any distance operator in the formulas. How both the curves came to their respective places? Why I need to understand is because I am not able to chalk out how to place curves for the 2nd or 3rd column. Please offer some hints so that I am able to do it and show you the final product. Already my data file is 4 MB; how to send it back to you? Thanks.
Did you update the formula references for the next set of Min/Max values of the new data set and the position of the lines?
Dear Andy
Understood and done for the second column. Perfect! Now I am doing it for the third column. Wanted to attach it but because of size..... You are great teacher. Salute to you. While I do for the third column, I ask you one more question on the chart. I formatted the columns and I changed the original colors of intervals in the column. Color shown in the series index on the right side of the chart are original colors. My questions are:
1. Is it possible to change index colors to the colors currently seen in the column?
2. I do not want to keep so many in series index. I have used only 5/6 colors( symbols) in a column. Is it possible to restrict the number of series color index?
Hope I am able to make it clear what I want to do. Thanks and salute again.
Dear Andy
I could solve the issue as raised in my last post on legend. You know it all; so I will not go into the detail. On the main issue of charting I will come back again for your comment. Thanks.
You can not restrict but you can format. Either with code or manually you can format a series to have a required colour.
You can delete legend entries by selecting the legend and then selecting the legend entry text before deleting.
Hi Andy
I was away for a few days. Thanks to you the main issue of charting is solved. As you are generous in helping me I dare to ask you one more question related to the same chart.
In the stacked column you will notice I have used various colored symbol to denote the rocks. I formatted manually each data point in a column. It takes lot of time to change symbol one by one. I have used pattern fill of excel and my picture file (attached). Is it possible to automate the same by using a code so that I denote a number against an interval of a column and it takes the pattern or picture automatically?
Here one important point is whole series is not of same symbol. For example, say series number 20. It can have different pattern symbol for different columns.
I am attaching both excel file and my picture file. Pattern fill is in built with excel. Hope you will guide me as before.
Thanks again.
This will only apply picture fills. If the name of the fill in column D is blank or the file can not be located in the subfolder ,ltho_symbol, no fill will be applied.
Please Login or Register to view this content.
Hi Andy
I am facing a new problem. So far my y axis minimum was 0. Now I am trying to make a chart having start at 4600 instead of 0. Chart is plotted but I am not able make the scale. I have attached for you to see and guide.
Thanks.
Simply include a value to pad the values for 0 to 4000. The set the minimum value axis to 4000.
Dear Andy
I have done that and its fine. Now, I wanted to add two more stack. As I try to add series, I find it is asking for x,y values where as I have only single data point value for stack. How to add new stack value? Thanks.
Any new series you add will take on the characteristics of the last series, which is an xy-scatter.
So add a series. Change to primary axis, if on the secondary. And change chart type to stacked column. Then use the select data dialog to set correct range references.
Dear Andy
I could not exactly follow your guidance. What I have done is removed xy scatter plots. Only stack plot remains; added 2 more stacks. Thereafter added xy scatter series again. It would have been better if I could follow you. If you elaborate a bit -if time permits.
Not sure I can elaborate more.
Add new series.
Change to stacked column.
Assign correct data ranges.
If you remove the xy-scatter series first then any new series will be stacked columns. But then you will need to ...
Add new series.
Change to xy-scatter.
Assign correct data ranges.
Hi Andy
I want to put few straight lines on the right side of the stacked column denoting real X values for example for 1, 10, 100, 1000 so that viewer can estimate the value of curve at any point of its curvature. I have attached worksheet. Hope you understand. I have put one line for you to understand my plan. I know you will find some way out. I can put those lines manually. But you might have better idea. To remind you, the lines are to be based on real X values and not derived values. Thanks.
You can add another series as xy-scatter and plot the required points. Use data labels showing Y value.
The yellow bands can be done by adding further series to the secondary axis.
You just need to format alternate series No fill and transparent fill.
Hi Andy
You have taken a, b, c, d, e series. When I click on respective area in the chart, I can see what is `b` and `d` but I do not find a, c, e. Then why these a, c, e, series were added I am not able to follow. Please guide me to understand.
Again, when I want to add $K$8 series, I am asked X value and Y value instead of single item. I am not able to add the series. But I can if I remove XY scatter series. Please......
you can not see series a,c,e as they are formatted with no fill. They are part of a stacked chart and are requried in order to give the floating effect to series b and d.
This is the last time I will say it as 3 times really is plenty.
When you add a new series it will default to the last series' chart type.
Add the series
change the chart type
assign data
Hi Andy
Ok. I am trying.
This is my last post on this as I feel like I'm flogging a dead horse!
Add a series.
Select the series and change the chart type.
Set the range references for that series.
If you can not see or select the newly created series use the Selection dropdown list on the Format tab.
Thanks Andy. Done.
Hi Andy
You were telling me time and again and I was not able to understand. Reason enough to be unhappy.I am currently using excel 2007. Is the problem because of that? Or I am really a fool ! I could solve the problem by following steps:
1. Add series f without assigning any value.
2. Click Format. Select series f.
3. Click Design. Change chart type from scatter to stack.
4. Go to select data. Select f series. Edit f series by replacing series value with required one. And its done.
Hope you have cooled down.
I can select b, d, f in the chart and format them but I can not select a, c, e and format. Even when all these series are added, why this is so?
Hope you will reply. Thanks for your patience.
Hi Andy
In the chart I find y value of scatter plot does not match with Y axis value. For example y of xy scatter shows 4792 at 4800 gridline of Y axis. Where I have gone wrong I am not able to find out. Please help.
I can not see anything wrong with the chart in the workbook you posted.
Hi Andy
Thanks for your response. I have understood-my wrong impression could be due to very close valued data. I need to add 2 more curves on the right side of the column. I have added one. The curve is named `density`. I have assigned its min-max value as 1.35 and 1.50. Adding another one called `neutron` is complex because its value increases from right to left that is in reverse scale. Not only that, it has to be within the space of 1.35 to 1.50. But its minimum will start from the point of 1.5 and end at 1.35.
My data file has exceeded forum size limit. So I have added old file but the new density curve can not be seen there. Hence I have added another excel file containing density and neutron data. I have also added one JPEG file to show how the density & neutron curves are positioned in reversed scale.
How to do that? Do I have to use secondary X axis? Please guide.
Hi Andy
I think I solved it. I have taken density curve on primary axis with min/max value of 1.30/1.45. Then for Neutron curve I have taken secondary X axis with min/max value of 0.55/0.80. The plot seems to be fine. Thanks to you only that I could do it.
Hi Andy,
Is there any way of combining XYscatter plot with a stacked BAR plot? I am using Excel 2010, and it is not letting me combine them into a single chart.
I am also trying to plot a few series (in XY scatter type), and highlight specific portions of the series (stacked BAR type), However I need 1 of my series on a secondary y-axis.
So say, my problem is such: http://www.excelforum.com/excel-char...29#post2734129
But I want another series called Angle with data: 10, 15, 20, 23, 62. But this should be on the secondary y-axis, but should still be highlighted by the "Tunnel" section.
Is there any way of doing it ?
Thanks.
Is there any way of combining XYscatter plot with a stacked BAR plot?
Yes, you can combine those chart types although Excel 2010 doesn't seem to allow this combination.
You simply need to start with your XY scatter series' and add the bar chart series afterwards.
Regards
Holger
parthiyer101,
Welcome to the Forum, unfortunately:
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks