+ Reply to Thread
Results 1 to 5 of 5

Excluding #na data on horizontal axis in stacked chart

  1. #1
    Registered User
    Join Date
    04-09-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Excluding #na data on horizontal axis in stacked chart

    Attached file - tab chofatexpend...
    Combo chart with stacked data & HR data.

    Want/need X axis to ignore all NA data without user having to go in & edit the 'ranges' as to make the end output copy & paste viable for non-excel users. Am mostly interested in ignoring the NA's as to keep the graph visually simple as for a Physical Therapist to be explain it to a customer w/o extraneous data.

    I've been perusing all the assorted links that I can find on how to get a bar chart to ignore the data but none of the links address the challenge when the #NA is in the x axis...
    the #NA is based on if statements using na() to create the #n/a error which seems to be the first step every link indicates needs to be done to then be able to exclude the data.

    Thanks in advance for your thoughts/assistance.

    - gina
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Excluding #na data on horizontal axis in stacked chart

    Hi Gina,

    I don't claim to be an expert on these things, but I think your use of #N/A is not effective here! I tend to use it for line charts, not it can cause problems elsewhere - especially when you trying to set up Dynamic Range Names.

    Phase2.xlsm

    I have created a new workbook, but have created Named Ranges for the data you want to chart. These are dynamic ranges, and will grow with the data. Note - I deleted all the cells with #N/A

    (Note: When you enter a named range into a chart, you have to include the workbook name, or it won't work).

    The formulas on your chofat sheet must drive you nuts, as they relate to blocks of 20 rows - I tried to find another way, but got different answers because you start the average calc in the middle of the block (row 13) which I couldn't figure out, so gave up.

    I hope this is not too confusing! It is quite complex stuff if you're not familiar with range names etc.


    I hope this helps, please let me know!

    Regards,

    David

    If this has been of assistance, please advise. A little thanks goes a long way.
    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".

    If several people have responded, when you reply please make it clear WHO you are responding
    to by mentioning their name.

  3. #3
    Registered User
    Join Date
    04-09-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Unhappy Re: Excluding #na data on horizontal axis in stacked chart

    Thank David
    Yes the average piece of the project has been making it more challenging than I initially expected. I don't understand all the science
    behind it but the final 2 minutes of each 'block' is the output that is critical data points which is also why I was tasked with trying to simplify the output so a customer didn't have to wait as long for result/analysis of their performance testing.

    I see/get the dynamic path you went down. I kept running into a brick wall on dynamic charting using horizontal data so I really appreciate your adjustment to the data.

    I still am faced with a growing result set that has to be populated by this average statement but since I also have a xy scatter as part of this project I can maybe link via a reference with a iferror statement that leaves the cell blank if it's na() on another sheet....
    Will work on that next

    Thanks again

    - gina

  4. #4
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Excluding #na data on horizontal axis in stacked chart

    Phase2 v2.xlsm
    Hi Gina,

    Now that I understand why you are calculating the average the way you are, I have modified the way my formula works. I added a column called Mod to the needed data sheet - this effectively gives every row in the 20 row block a number from 0 to 19. This is then included in the AVERAGEIFS() formula so that it excludes the data you don't want. I now get the same answers!

    As for the xy scatter line - this may not be an issue now that the range name is dynamic, so you may be able to do away with the #N/A all together.

    Keep trying - I think it will be a lit easier for you now to use my formula and copy it across rather than edit it in every cell.

    I hope this helps, please let me know!

    Regards,

    David

    If this has been of assistance, please advise. A little thanks goes a long way.
    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".

    If several people have responded, when you reply please make it clear WHO you are responding
    to by mentioning their name.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-09-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Excluding #na data on horizontal axis in stacked chart

    Thanks again David
    I've never really used averageifs but clearly it's a command worth understanding.

    Really appreciate the extra add on tip.

    Gina

+ 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] Dates by Month on X-Axis in Horizontal Stacked Bar Chart
    By Bandolier2k in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 05-16-2018, 11:02 PM
  2. Stacked Bar graph with two horizontal axis??
    By jschneiter2 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 11-15-2013, 12:56 AM
  3. Stacked bar chart with stacked secondary axis markers - please help!
    By Boardlady in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-22-2013, 10:32 AM
  4. Replies: 1
    Last Post: 05-30-2013, 10:03 AM
  5. Trying to create a stacked column chart with data above and below the x-axis
    By funkmeister79 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-24-2013, 10:54 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