+ Reply to Thread
Results 1 to 3 of 3

Dates on line graph X-axis not in normal order

  1. #1
    Registered User
    Join Date
    07-28-2013
    Location
    Georgia
    MS-Off Ver
    Excel 2010, 2013
    Posts
    8

    Dates on line graph X-axis not in normal order

    Looking for help again.
    I have two workbooks. One is for daily data entry. The second, I have pulled data from the data sheet, and this data will be graphed in the same workbook.
    The problem I am having is that the x-axis is the dates from the data sheet. They are not always in 1,2,3 order and most of them are duplicated (due to 2 shifts on each day), and I need it to graph that way. If I choose the date format, it goes 1-30 and skips a lot of data. If I choose as text, it shows all the blanks. I know I can manually filter them out and manually select the date span, but I am trying to make this completely dynamic (as some people reviewing the chart may not be excel savvy). It works by choosing the starting date on the "Data" tab on the Chart Sample sheet. On the graph tab I have two sample graphs (using the same data) to show some of the things I have tried.
    I have tried the NA() but the graph, graphs the NA. I have tried a bunch of different things.
    I have attached the two sheets. The chart sample sheet is protected (so i didn't screw up previous progress) but there is no password.
    Any help is greatly appreciated.

    Thank you

    Edit: I forgot to mention, this will be primarily used in excel 2010.
    Attached Files Attached Files
    Last edited by ppidgursky; 12-25-2017 at 09:26 AM.

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

    Re: Dates on line graph X-axis not in normal order

    If I understand what you are trying to do, I think you want to use the second chart (with the text/category axis) coupled with dynamic named ranges for the chart data series (especially the horizontal category axis data). Dynamic charts using dynamic named ranges are an old topic in Excel circles. A couple of pages to get you started:
    http://www.excel-easy.com/examples/d...med-range.html
    https://peltiertech.com/dynamic-charts/

    Most dynamic named ranges are built using the OFFSET() function, so there is value in becoming vary familiar with this function: https://support.office.com/en-us/art...e-b4d906d11b66

    A key part of the OFFSET() function will be the "width" parameter, which will usually be some kind of counting function (COUNT() or COUNTIFS(), probably). https://support.office.com/en-us/art...c-aa8c2a866842

    Does that help?
    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
    07-28-2013
    Location
    Georgia
    MS-Off Ver
    Excel 2010, 2013
    Posts
    8

    Re: Dates on line graph X-axis not in normal order

    That was very tedious but also very informative. I thank you for your time. These links gave me exactly what I needed.
    Using name ranges, the CountIfs and the offsets did the trick. Also, and most important was using the "?*". It made the cell appear to be blank allowing the range to work and truly be dynamic.

    This one taught me a bunch.. Thank you again.
    i.e... =OFFSET(Data!$A$2,0,2,1,COUNTIF(Data!$C$54:$BN$54,"?*"))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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