+ Reply to Thread
Results 1 to 6 of 6

Plotting data points on chart in correct position without a label on the x axis

  1. #1
    Registered User
    Join Date
    01-28-2014
    Location
    seattle
    MS-Off Ver
    Excel 2007
    Posts
    22

    Plotting data points on chart in correct position without a label on the x axis

    I would like a line chart with dates/times across the x axis. This would correspond to some test values.

    I'd also like to plot some interventions that took place at different times than the values were recorded. But, I don't want the intervention times to be labeled on the x axis, while I do want the dots representing those interventions to be in the correct position.

    I've attached a sample worksheet.

    Can this be done?

    Thanks.
    Attached Files Attached Files

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

    Re: Plotting data points on chart in correct position without a label on the x axis

    Short answer: Use an XY scatter chart rather than a line chart.

    Specific things to note in your specific case:
    1) Note the text value in A5 and change it so it will be a real date/time serial number.
    2) Edit the two intervention series (Source Data dialog) so that each of those series will use the appropriate cells for the x axis values
    3) Excel's auto setting for the x axis limits will probably be wrong, so go in and set the axis limits to reasonable values. This will probably be easiest if you delete the number formatting from the cells and/or the axis so you can see the actual date/time serial numbers to choose reasonable values for the axis limits. You can restore the date/time formatting once you have these values entered.
    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
    01-28-2014
    Location
    seattle
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Plotting data points on chart in correct position without a label on the x axis

    I have the data in two worksheets, set up a little differently. Are your instructions referring to the layout in the Test 1 worksheet or Test 2?

    I am not sure what you mean in Step 2.
    The interventions occur at different times than the values were taken, does "appropriate cells for the x axis values" refer to the time the value was taken or the date/time of the intervention.

    Now I've got the charts in a state such that the x axis display all times as 12:00, and the option to change them to Text under Axis Options is not available. grrr.

    Thanks

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

    Re: Plotting data points on chart in correct position without a label on the x axis

    I had only looked at test 1. Looking at test 2, I see essentially the same steps -- change chart type to XY scatter -> edit data series as needed -> format axis as needed -> other formats as needed.

    I am not sure what you mean in Step 2.
    The interventions occur at different times than the values were taken, does "appropriate cells for the x axis values" refer to the time the value was taken or the date/time of the intervention.
    I'm not sure I understand. On Test 1, I would have used A16 as the X value for intervention 1 and A22:A25 as the X value for intervention 2. On Test 2, Use A2:A14 or A2:A15 (Y values are different for the different series, not sure if they were intended to be) for the X values.

    Now I've got the charts in a state such that the x axis display all times as 12:00, and the option to change them to Text under Axis Options is not available. grrr.
    We can recover the text axis if desired, but I think I would need to understand why you want the text axis and you don't want the data spaced out according to the date/time value.

  5. #5
    Registered User
    Join Date
    01-28-2014
    Location
    seattle
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Plotting data points on chart in correct position without a label on the x axis

    Thank you. I did what you suggested, and attached a new copy of the workbook.

    The chart looks good, except what I ultimately want is the dates/times across the bottom to be the dates/times that are in Cells A2 to A11.

    The dots for the interventions would appear in the appropriate spot on the chart, but I don't want their times to be displayed across the bottom.
    Attached Files Attached Files

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

    Re: Plotting data points on chart in correct position without a label on the x axis

    The chart looks good, except what I ultimately want is the dates/times across the bottom to be the dates/times that are in Cells A2 to A11.
    Off the top of my head, I see two possible approaches:

    1) Use data labels. Select the "value" series -> add data labels (either right click and select from context menu or Chart Tools -> layout -> Data Labels) -> Select data labels and format to show X values only, position above/below, on alignment tab rotate text 270. This is probably the easier option, but the data labels will be positioned inside of the chart with the data points. The axis can be left alone at the bottom to show half day increments (or whatever the axis is formatted to show).

    2) Use a dummy series with data labels along the X axis. Add a column of 0's (in Test 1, fill C2:C11 with 0. In test 2, enter 0 into E2:E3,E6:E7, etc. or use a formula like =IF(ISBLANK(B2),NA(),0)) -> Add the dummy series to the chart (Select Source data dialog) -> Select the dummy series -> Add data labels -> Same process as (1) to get the correct data labels formatted -> If desired, format axis to have no labels.

    I think (1) is easier, because you don't need the dummy series to position the X labels along the axis.

+ 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. Replies: 2
    Last Post: 02-07-2014, 12:18 PM
  2. [SOLVED] Bar Chart Data label % figure not correct
    By Lsinshirley in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-03-2013, 09:59 AM
  3. Replies: 1
    Last Post: 05-30-2013, 10:03 AM
  4. Replies: 1
    Last Post: 05-05-2013, 10:38 PM
  5. [SOLVED] Chart Data Label Position
    By Lady_Shaz in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 12-20-2012, 05:07 AM
  6. XValue Data Points Not Plotting Correct Values
    By shephejm in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-24-2009, 08:42 PM
  7. Pie Chart plotting 1 single color for multiple data points
    By moonseed in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-12-2008, 03:41 PM
  8. Chart not plotting new data anymore, source data correct.
    By mr2mike in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-29-2008, 07:25 PM

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