+ Reply to Thread
Results 1 to 10 of 10

X-axis dates longer than y-axis values which are not na(). Results in messed up chart.

  1. #1
    Forum Contributor
    Join Date
    11-13-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    279

    X-axis dates longer than y-axis values which are not na(). Results in messed up chart.

    I have attached a file to explain the problem better. You can see from the attached chart that because I have na() value, the y-axis is plotting against x-axis values (dates) incorrectly. Is it possible to resolve this problem. Sorry I dont think I can explain it better than with the attached file.
    Any help greatly appreciated. I always this issue. Thank you.
    Attached Files Attached Files

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

    Re: X-axis dates longer than y-axis values which are not na(). Results in messed up chart.

    I am not sure what your question really is, so it is unfortunate that you cannot explain it better.

    What I see is that all of column H is N/A, because the MAX() function that looks at column F returns N/A, because there are N/A's at the end of this column and the MAX() function does not ignore error values. Column F returns N/A error when column E has N/A N/A text string. Perhaps change the formula in column F so that it returns a text string instead of the N/A errors. Then the MAX function can ignore the those values and return a correct Max value.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    11-13-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: X-axis dates longer than y-axis values which are not na(). Results in messed up chart.

    Hi Mr Shorty.
    Thanks so much for your reply.
    Now indeed I do want all the na()s that my formulas have made. The issue is that if you look at the two lines in the chart. For some reason there appears to be no y-axis data for September 2015. When clearly there should be from the data source.
    Does that make sense. Its the weird chart that is the issue.
    Please let me know if that is still not clear.
    Thanks so much,

    David

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

    Re: X-axis dates longer than y-axis values which are not na(). Results in messed up chart.

    Now I see the problem. Unfortunately, I do not immediately see a solution.

    You should not be up against any Excel limits that I am aware of. A few observations:

    1) If I filter the data so it starts in 2008, then it appears to plot all of the data. As I add the years before 2008, it starts to cut off the later data.
    2) If I copy and paste the three columns (A, G, K) and paste as values in a new sheet, it seems to plot all the data without difficulty or error.
    3) If I delete the third data series, it seems to plot all of the data.

    I have no explanation at this point.
    Last edited by MrShorty; 09-21-2015 at 05:03 PM.

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: X-axis dates longer than y-axis values which are not na(). Results in messed up chart.

    Your data ranges are not the same size as the category ranges. If you adjust that, the chart should be fine.
    Remember what the dormouse said
    Feed your head

  6. #6
    Forum Contributor
    Join Date
    11-13-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: X-axis dates longer than y-axis values which are not na(). Results in messed up chart.

    Hi folks. The problem is my data ranges change every day as the input data updates. Therefore I absolutely want to avoid having to change the data ranges for the charts manually every day since I have a number of similar files. This is why I use named ranges. Any other ideas? really appreciate you helping me through the nightmare

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: X-axis dates longer than y-axis values which are not na(). Results in messed up chart.

    I don't see the problem. You can use named ranges - they just need to all be the same size.

  8. #8
    Forum Contributor
    Join Date
    11-13-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: X-axis dates longer than y-axis values which are not na(). Results in messed up chart.

    The problem is I am using offset functions so that the named range is always as long as the column of data. (Which is necessary so that it pulls in the additional rows printed daily). I believe you can see the offset function i have used for my named range in the attached. Thanks.

  9. #9
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: X-axis dates longer than y-axis values which are not na(). Results in messed up chart.

    Create a named range for the categories, then define all the other ranges as an OFFSET from that range. That way they will all be the same size.

  10. #10
    Forum Contributor
    Join Date
    11-13-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: X-axis dates longer than y-axis values which are not na(). Results in messed up chart.

    Worked a treat, thanks romperstomper

+ 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. [SOLVED] set values as x axis and y axis in excel chart and name points
    By christopherL in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-27-2014, 10:19 AM
  2. Bubbel Chart with non-numerical values for both X axis and Y axis
    By air15567 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-29-2014, 11:25 AM
  3. Replies: 10
    Last Post: 07-21-2014, 11:11 AM
  4. Replies: 1
    Last Post: 03-01-2014, 07:10 AM
  5. Why does my chart look weird if I use dates as the 'x' axis values?
    By htroberts in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 11-07-2012, 03:36 PM
  6. [SOLVED] how to put x axis values in the chart instead of x axis labels
    By Sinclair in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-21-2005, 06:05 AM
  7. How do I change X-Axis values in a chart with 2 Y-Axis?
    By ESGLCC in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-04-2005, 10:06 AM

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