+ Reply to Thread
Results 1 to 14 of 14

Bar Chart - Ignore NA# or 0s

  1. #1
    Forum Contributor
    Join Date
    08-22-2017
    Location
    USA
    MS-Off Ver
    2016 WINDOWS and MAC
    Posts
    294

    Bar Chart - Ignore NA# or 0s

    Hi,

    Can someone please help me out with having a bar cart ignore NA#? I basically just need a bar chart that counts unique values and ignores the x-axis labels when #NA or NO DATA is present in the list. The list is dynamic, so if the user does not enter data, it will return NA# or 0. I can't get the bar chart's x-axis to ignore any of these no matter how I do it.

    The chart simply needs to dynamically display the count on y-axis and the value on the x-axis.

    I have attached a sample. I prefer no VBA. But can use his necessary.

    Thank you for your time.


    FILE:
    Attached Files Attached Files
    Last edited by ironfelix717; 11-20-2017 at 12:32 PM.

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Bar Chart - Ignore NA# or 0s



    To attach an Excel file to your post,
    • click Go Advanced,
    • scroll down until you see Manage Attachments,
    • click that and select Browse,
    • select your file and click Open,
    • click Upload and you will see your attachment below Upload Files from a website
    • click Close this window,
    • click Submit reply

    After that you should see attachment in your post

  3. #3
    Forum Contributor
    Join Date
    08-22-2017
    Location
    USA
    MS-Off Ver
    2016 WINDOWS and MAC
    Posts
    294

    Re: Bar Chart - Ignore NA# or 0s

    Bump.. Anyone? Not sure if this is possible.

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Bar Chart - Ignore NA# or 0s

    maybe change NA() to "" but it will not change length of x-axis (there will be gaps instead of values)
    Last edited by sandy666; 11-21-2017 at 04:46 PM. Reason: typo

  5. #5
    Forum Contributor
    Join Date
    08-22-2017
    Location
    USA
    MS-Off Ver
    2016 WINDOWS and MAC
    Posts
    294

    Re: Bar Chart - Ignore NA# or 0s

    Hi,

    I prefer to not have gaps in the charts. Is there another solution?


    Thank you

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Bar Chart - Ignore NA# or 0s

    Use IFNA() instead of IFERROR()

    IFNA(your_formula, "") and Control+Shift+Enter

    but I don't think it will change anything

    You can try with autofilter but in this case it doesn't work (two different ranges in the same place)

    maybe try with vba and on #N/A - hide these rows
    I'm far away from vba so you need to ask about it on VBA forum (e.g. here)
    Last edited by sandy666; 11-21-2017 at 06:59 PM.

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Bar Chart - Ignore NA# or 0s

    You need to use named formula to define dynamic range for labels and data.

    The two named ranges can then be used in the series formula rather than static cell references.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  8. #8
    Forum Contributor
    Join Date
    08-22-2017
    Location
    USA
    MS-Off Ver
    2016 WINDOWS and MAC
    Posts
    294

    Re: Bar Chart - Ignore NA# or 0s

    Andy,

    Your spreadsheet does exactly as I intend, i'm just confused on what the solution is, i see the range changing dynamically. I see the series formula uses "my labels" and "my data". However, I'm not quite sure where this is coming from.

    Is the solution simply the series formula? Or is there more that im not seeing.

    Thank you!

  9. #9
    Forum Contributor
    Join Date
    08-22-2017
    Location
    USA
    MS-Off Ver
    2016 WINDOWS and MAC
    Posts
    294

    Re: Bar Chart - Ignore NA# or 0s

    UPDATE:

    So i've just now learned about "Named Ranges", wish I had known about these before... However, back to the problem at hand, I cannot find where you actually named the data range. Wouldnt the name of the range be indicated inthe formula bar? If not, i understand you can name a range in a formula. But i'm not sure on the syntax.


    Thank you

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Bar Chart - Ignore NA# or 0s

    first: Name Manager (or Ctrl+F3)
    second: Chart Tools - Design - Select Data - Edit
    Last edited by sandy666; 11-22-2017 at 10:08 AM.

  11. #11
    Forum Contributor
    Join Date
    08-22-2017
    Location
    USA
    MS-Off Ver
    2016 WINDOWS and MAC
    Posts
    294

    Re: Bar Chart - Ignore NA# or 0s

    Sandy,

    I am currently setting up the named ranges in my workbook and will report back when the issue is solved. Thank you all for the help.

  12. #12
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Bar Chart - Ignore NA# or 0s

    As Sandy666 says, use Name manager on the Formulas tab. Once the names are defined you can modify the sereis formula via Select data or directly in the formula bar.

  13. #13
    Forum Contributor
    Join Date
    08-22-2017
    Location
    USA
    MS-Off Ver
    2016 WINDOWS and MAC
    Posts
    294

    Re: Bar Chart - Ignore NA# or 0s

    All,

    Andy's solution works exactly as intended. Thank you all for the help.

    Greatly appreciated!!!

  14. #14
    Registered User
    Join Date
    08-26-2021
    Location
    South Korea
    MS-Off Ver
    2013
    Posts
    1

    Re: Bar Chart - Ignore NA# or 0s

    Tks Andy! It works like a charm. ^-^

+ 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. Chart to Ignore 0 Values
    By mysticmoron109 in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 03-18-2017, 01:11 AM
  2. How to get a pareto chart to ignore 0
    By Kevin62 in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 06-17-2016, 11:06 AM
  3. ignore zeros in pivot chart
    By ammartino44 in forum Excel General
    Replies: 11
    Last Post: 09-11-2015, 10:57 AM
  4. Chart, ignore blanks
    By rwernlund in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-12-2015, 01:56 PM
  5. Chart to Ignore NA or 0
    By Rasterty in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-25-2013, 02:22 AM
  6. How to ignore errors in a bar chart
    By titanium in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 01-29-2010, 12:43 PM
  7. Chart to ignore zero values - can it be done
    By nigelog in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-27-2008, 10:18 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