+ Reply to Thread
Results 1 to 5 of 5

Stacked Clustered Bar Ignoring Null Values

  1. #1
    Registered User
    Join Date
    06-01-2015
    Location
    Derby, England
    MS-Off Ver
    2013
    Posts
    87

    Stacked Clustered Bar Ignoring Null Values

    I need to create a chart that queries two columns and ignores the rows when both are zero. Hopefully the attached example will be clearer.

    I have attached a dummy dataset and two charts, one where I have got to and one where I would like to get. I've managed to auto filter and sort only the required fields and am trying to use named ranges in the chart, its just that last step I'm missing.

    Any and all help is greatly appreciated.

    Thank you,

    Ed
    Attached Files Attached Files

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

    Re: Stacked Clustered Bar Ignoring Null Values

    It's not clear to me exactly where you are stuck. I notice that your "desired" chart has series in rows, but your "calculated" has a mix of series in columns with category data in a row. I think you will get what you want by simply improving/simplifying the chart's source data range. Here's what I did (in 2007, so your UI's may be different):

    1) To get the category data adjacent to the series data, I entered =B1 into I1, then copied that into J1.
    2a) Selected the "Calculated" chart -> Select Data (note that the "source data" field is empty because the source data range is too complex to display here)
    2b) Select the source data field, then specify I1:J7 as the chart's source data range
    2c) Check to see how Excel interpreted the source data (series in columns or rows, specifically). If Excel has the 6 data series correctly in rows, then exit dialog. If Excel has 1 or 2 data series in columns, then click on the "switch rows/columns" button.

    At that point, I was really close to your desired chart output. As noted, I'm using 2007, so MSFT may have moved and restructured these dialogs, but those are the steps I would expect you to go through.
    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
    06-01-2015
    Location
    Derby, England
    MS-Off Ver
    2013
    Posts
    87

    Re: Stacked Clustered Bar Ignoring Null Values

    MrShorty, Thank you, this i is almost perfect. Where it falls down for me is that it still shows the blank series in the legend. This is what I'm trying to get away from.

    Your help is much appreciate though.

    Ed

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

    Re: Stacked Clustered Bar Ignoring Null Values

    Excel does not offer a lot of flexibility in the legend. I would replace the legend with a dummy XY series and data labels (see tutorial here: https://peltiertech.com/double-legen...-single-chart/ ). The main change for you would be that you won't need the 3rd party add-in to get the data labels, since that feature was introduced natively to Excel in 2013. There will be some tedium the first time getting the dummy series formatted and getting the right formulas for these points in the spreadsheet so that "errors" don't show up (you might review this one about hiding points in XY scatter charts: http://peltiertech.com/mind-the-gap-...g-empty-cells/ ). Once it is set up, however, it should update as data are added/changed without further interference.

    Let us know if you have trouble

  5. #5
    Registered User
    Join Date
    06-01-2015
    Location
    Derby, England
    MS-Off Ver
    2013
    Posts
    87

    Re: Stacked Clustered Bar Ignoring Null Values

    Mr Shorty, thanks again, I really appreciate the help. Unfortunately the intention of this is top create a template so that multiple report creators plug in some numbers and a chart is spat out. The method in the link is actually more effort that going into the chart and switching of the null series s
    o doesn't work for us in this instance.

+ 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