+ Reply to Thread
Results 1 to 7 of 7

Excel Chart that combines a sectional bar and has a plotted point

  1. #1
    Registered User
    Join Date
    11-06-2012
    Location
    Manchester
    MS-Off Ver
    Excel 2003
    Posts
    6

    Excel Chart that combines a sectional bar and has a plotted point

    Hi All,

    Hoping that someone would be able to help me with this or just plain tell me that it cant be done but here goes.

    It would help to say what project I am doing.

    I have to create a bar chart that shows centiles in the following sections:
    <2
    2 - 90
    91 - 97
    >98

    Then I need to have a dot plotted within that bar chart to show where each centile falls.

    What I need is to create a bar chart with sections that would be labelled (these sections would be the same all of the time) and then have a dot plotted on the bar chart to display a figure.


    When I have figured that out I then need to find a way to use that chart in a Mail Merge letter.

    In the mail merge the sections would remain the same but the plot would change depending on the person's centile.


    Let me know if you need any further information.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Excel Chart that combines a sectional bar and has a plotted point

    I am not able to visualize exactly what you are trying to do. I might guess that you are looking for some kind of column/line combination chart or an area/line combination chart. Review this page: http://peltiertech.com/excel-charts-...izontal-bands/ and see if any of these charts is similar to what you are looking for.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    11-06-2012
    Location
    Manchester
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Excel Chart that combines a sectional bar and has a plotted point

    Thank you for your reply MrShorty.

    I believe some more information would help then.

    I have set up a spreadsheet that calculates the BMI Centile of the people whose data is inputted into the spreadsheet.

    The spreadsheet has some Macros and VBA in it that opens up a Word Document and starts a Mail Merge with the data to send the information with the height, weight and BMI Centile to the relevant person.

    BMI Centiles have the following categories:
    <2 - Underweight
    2 - 90 - Healthy Weight
    91 - 97 - Overweight
    >98 - Very Overweight

    I have been asked whether a bar chart / graphic (sectioned as above) could be inserted into a new letter document and then the BMI Centile of the person would be plotted on the bar.

    The way I saw it working was to have the chart in Excel and then pull the chart to the Mail Merged document using VBA. However I am having some trouble with initially creating the chart to be pulled over.

    I have also been thinking of other ways this could be accomplished, without the use of an Excel chart - but I haven't come up with any brilliant ideas as of yet.


    Hope that helps.


    Please let me know if you require any further information.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Excel Chart that combines a sectional bar and has a plotted point

    The way I saw it working was to have the chart in Excel and then pull the chart to the Mail Merged document using VBA. However I am having some trouble with initially creating the chart to be pulled over.
    Which makes sense to me, though I will be useless on the Mail Merge part of the operation. So I will focus on creating the chart.

    I'm still not sure what you want the chart to look like. When I suggested Jon's tutorial, I was thinking of something like the final examples he gives at the end of that tutorial. Colored horizontal bands to indicate the desired percentiles, combined with line/scatter charts to show the individual data points. I cannot tell if you are rejecting this sort of chart for your data or not.

  5. #5
    Registered User
    Join Date
    11-06-2012
    Location
    Manchester
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Excel Chart that combines a sectional bar and has a plotted point

    Hi MrShorty,

    Thank you again for your reply.

    I have just been able to look at the tutorial.

    In hast I replied to your post with further information, sorry about that.

    With regards to the tutorial though, I think that would be great, I was originally thinking about a bar chart (horizontal), however a vertical chart will work just as well.

    Would you be able to inform me if there is a possibility to have only one data point in the chart, as for each letter there would only be one centile that I would be working with.


    Thank you


    S

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Excel Chart that combines a sectional bar and has a plotted point

    Short but useless answer, I'm sure it is possible to put only one data point and one percentile band in the chart (though, at that point, I'm not sure what the point of the chart is). It should be fairly straightforward if you follow Jon's instructions -- inserting your own data in for the example data he used. Then format and adapt your chart to your specific requirements.

    I'd probably suggest at this point that you attempt to recreate Jon's example chart to make sure you understand how he puts it together.

  7. #7
    Registered User
    Join Date
    11-06-2012
    Location
    Manchester
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Excel Chart that combines a sectional bar and has a plotted point

    Thank you MrShorty for all of your assistance in this.

    The chart tutorial that you supplied me with is exactly the type of thing I have been looking for.

    And I can assure you that the answer that you gave was not useless at all.

    Now I just have to work out how to include in the original VBA to copy the chart as a static image (to ensure that the data stays the same) and be displayed properly on each document within the Mail Merge.

    I'll be sure to come back onto the Forum if I require any further help.


    S

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Cross sectional regression in excel?
    By Ystar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-25-2013, 10:50 AM
  2. Cross-sectional Chart
    By Soupy11 in forum Excel General
    Replies: 1
    Last Post: 11-13-2010, 05:07 PM
  3. Power Point Chart linked to Excel Chart
    By pr4t3ek in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 09-24-2009, 11:33 AM
  4. how to get interpolated data point values from plotted graph
    By cal_hob in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-24-2006, 03:50 PM
  5. Chart with a plotted point inside an envelope.
    By fewest in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-23-2005, 12:05 PM

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