+ Reply to Thread
Results 1 to 5 of 5

Dynamic chart where zero values are not included

  1. #1
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    807

    Dynamic chart where zero values are not included

    I am using the 'standard' dynamic range chart in Excel 2010 (thank you Jon and Deborah) . I am getting my data range through a formula which will sometimes bring in ranges, say, A - D, and sometimes ranges A- F. If there is no value in the last row/s I do not want them to populate the chart with zero values AND I do not want the names or spaces to appear in the horizontal axis.

    I have a attached a sample and thanks for any ideas how to solve this.
    Attached Files Attached Files
    Last edited by BRISBANEBOB; 11-24-2012 at 06:44 PM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Dynamic chart where zero values are not included

    Change your dynamic named range for XLabels to

    =Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))
    HTH
    Regards, Jeff

  3. #3
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Dynamic chart where zero values are not included

    Hi Bob,

    Also in A2 copied across and down >> =IF(SUM($H21:$K21),G21,NA())

  4. #4
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    807

    Re: Dynamic chart where zero values are not included

    Hi Jeff

    Firstly, thanks very much. Perfect!

    I understand the formula but not the "=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))"

    If you've got a minute, can you tell me how it works?

    Thanks again

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Dynamic chart where zero values are not included

    Hi Bob,

    You are welcome, glad it worked out for you and thanks for the feedback.

    Basically the formula says start at A2 and look down the column until it finds a text that is as large as a word with 255 Zs, but off course can't find anything that big so just gives you the last word in the column.

    Maybe this will help even more than I can probably explain...

    http://www.xldynamic.com/source/xld....html#last_text

+ 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