+ Reply to Thread
Results 1 to 4 of 4

Series is using wrong X-axis values in dynamic chart

  1. #1
    Registered User
    Join Date
    03-21-2015
    Location
    Milwaukee
    MS-Off Ver
    2010
    Posts
    16

    Question Series is using wrong X-axis values in dynamic chart

    I'm trying to create a chart that has a dynamic domain of X-axis values using a nested IF function IF("","",IF(max-value,"",formula)).
    See cell B16 in the attached file for the actual IF function.

    The red series charts the X-values in B15 to B47 against the Y-values in E15 to E47.
    The red series is good.

    The blue series charts the same values as the red series plus one additional "blank" row, row 48, (which isn't actually "blank" but contains the nested IF function.)
    The blue series is wrong.
    It uses the position in the list (1 2 3 4 5....33) as the X-values for the blue series rather than the X-values from B15 to B48.

    What am I doing wrong?

    Graph problem.JPG
    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,802

    Re: Series is using wrong X-axis values in dynamic chart

    Excel sees "" as a text string, and causes it to see all of the X values for that series as text and ignore any numbers.

    I would suggest this essay: http://peltiertech.com/mind-the-gap-...g-empty-cells/ -- especially the section on "simulating empty cells". For XY scatter charts, a better strategy would be to return NA() rather than empty string "".
    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
    03-21-2015
    Location
    Milwaukee
    MS-Off Ver
    2010
    Posts
    16

    Re: Series is using wrong X-axis values in dynamic chart

    Thank you, Mr. Shorty. Returning NA() solved the problem with the X-values turning into text, which turned out to be my problem.
    The essay was very good.
    Sadly, I'm left with a spreadsheet full of #N/A, but that's the price of progress I suppose.

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

    Re: Series is using wrong X-axis values in dynamic chart

    Sadly, I'm left with a spreadsheet full of #N/A, but that's the price of progress I suppose.
    I suppose you have to decide how badly you don't want to see the N/A's. In cases where I really do not want to see the N/A's, I will have two tables -- one table with the N/A's for the chart to look at, and a second table without the N/A's for me to look at. This second table is usually made up of a formula like =IF(ISNA(A1),"",A1), which simply copies the table. Then I have a table that I like to look at, and the chart has a table that it likes to look at, and we're both happy.

+ 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: 5
    Last Post: 03-28-2015, 03:48 PM
  2. Column and Scatter/Smoothed Line Chart - X-axis values appear in wrong place
    By Cheekybeelzebub in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-20-2015, 09:33 AM
  3. RE: Moving X Axis and Series Values in chart
    By statsfun in forum Excel General
    Replies: 0
    Last Post: 09-13-2012, 11:41 AM
  4. [SOLVED] Y-Axis Displaying Wrong Values for Data Series! Help!
    By smadey in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-01-2012, 10:12 AM
  5. VBA: Some series in chart have wrong x values
    By smacdav in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-03-2011, 07:32 AM

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