Closed Thread
Results 1 to 42 of 42

Stacked column combined with xy scatter plot.

  1. #1
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Stacked column combined with xy scatter plot.

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

  2. #2
    Valued Forum Contributor SDruley's Avatar
    Join Date
    04-27-2009
    Location
    Conover, NC
    MS-Off Ver
    Excel 2010 64 bit
    Posts
    415

    Re: Stacked column combined with xy scatter plot.

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

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,978

    Re: Stacked column combined with xy scatter plot.

    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.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  4. #4
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

    Quote Originally Posted by Andy Pope View Post
    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.

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,978

    Re: Stacked column combined with xy scatter plot.

    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.

  6. #6
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

    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

  7. #7
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

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

  8. #8
    Valued Forum Contributor SDruley's Avatar
    Join Date
    04-27-2009
    Location
    Conover, NC
    MS-Off Ver
    Excel 2010 64 bit
    Posts
    415

    Re: Stacked column combined with xy scatter plot.

    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.

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,978

    Re: Stacked column combined with xy scatter plot.

    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.

  10. #10
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

    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.

  11. #11
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

    Hi Andy
    2 sets of x values and associated y values are ready and I have attached. Please have a look.
    Thanks.
    Attached Files Attached Files

  12. #12
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,978

    Re: Stacked column combined with xy scatter plot.

    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.
    Attached Files Attached Files
    Last edited by Andy Pope; 11-07-2011 at 05:43 AM.

  13. #13
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

    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.

  14. #14
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,978

    Re: Stacked column combined with xy scatter plot.

    32K data points per series.

  15. #15
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

    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.

  16. #16
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,978

    Re: Stacked column combined with xy scatter plot.

    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?
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

    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.

  18. #18
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

    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.

  19. #19
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,978

    Re: Stacked column combined with xy scatter plot.

    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.

  20. #20
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

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

  21. #21
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,978

    Re: Stacked column combined with xy scatter plot.

    Please Login or Register  to view this content.
    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.

  22. #22
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

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

  23. #23
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,978

    Re: Stacked column combined with xy scatter plot.

    Simply include a value to pad the values for 0 to 4000. The set the minimum value axis to 4000.
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

    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.

  25. #25
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,978

    Re: Stacked column combined with xy scatter plot.

    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.

  26. #26
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

    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.

  27. #27
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,978

    Re: Stacked column combined with xy scatter plot.

    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.

  28. #28
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

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

  29. #29
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,978

    Re: Stacked column combined with xy scatter plot.

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

  30. #30
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

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

  31. #31
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,978

    Re: Stacked column combined with xy scatter plot.

    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

  32. #32
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

    Hi Andy
    Ok. I am trying.

  33. #33
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,978

    Re: Stacked column combined with xy scatter plot.

    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.

  34. #34
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

    Thanks Andy. Done.

  35. #35
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

    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.

  36. #36
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

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

  37. #37
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,978

    Re: Stacked column combined with xy scatter plot.

    I can not see anything wrong with the chart in the workbook you posted.

  38. #38
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

    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.
    Attached Images Attached Images
    Attached Files Attached Files

  39. #39
    Registered User
    Join Date
    11-02-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Stacked column combined with xy scatter plot.

    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.

  40. #40
    Registered User
    Join Date
    03-15-2012
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Stacked column combined with xy scatter plot.

    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.

  41. #41
    Registered User
    Join Date
    11-30-2011
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: Stacked column combined with xy scatter plot.

    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

  42. #42
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Stacked column combined with xy scatter plot.

    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.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1