+ Reply to Thread
Results 1 to 10 of 10

Excel Chart to Ignore Cells with Formulas Which Return #N/A

  1. #1
    Registered User
    Join Date
    04-30-2010
    Location
    washington dc
    MS-Off Ver
    Excel 2007
    Posts
    51

    Excel Chart to Ignore Cells with Formulas Which Return #N/A

    Hi.

    So, i've gone through several iterations of trying to solve this problem. I have a chart pulling data from a range of cells with formulas in them. Some of the cells have formulas which currently return #N/A, but which may in the future return a numerical value. I need a chart which:

    1) Does not chart cells which return #N/A
    2) Does chart new cells within a defined range once they stop returning #N/A and return a value instead.


    I have tried using formulas using na() when the formula is false. This doesn't seem to work.

    In addition, I have tried to set a dynamic range, using the countif(range,">0"). Also does not seem to work.

    Help! I've attached the workbook.
    Ignore NA in Chart.xlsx

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

    Re: Excel Chart to Ignore Cells with Formulas Which Return #N/A

    You need to greate a named range that is restricted to valid data points.
    The use that as the series range.

    Use Formulas > Name Manager. to see named range
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    04-30-2010
    Location
    washington dc
    MS-Off Ver
    Excel 2007
    Posts
    51

    Re: Excel Chart to Ignore Cells with Formulas Which Return #N/A

    Hi Andy -

    Thanks for your help.

    Unfortunately, when I followed your same structure in the actual sheet (The one I sent you was a copy), I still get an error message. I've attached the actual sheet here. What am I missing?

    Jacob
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-30-2010
    Location
    washington dc
    MS-Off Ver
    Excel 2007
    Posts
    51

    Re: Excel Chart to Ignore Cells with Formulas Which Return #N/A

    Hi Andy - Thanks for your help. Unfortunately still running into problems. See attached below.

    Jacob

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,858

    Re: Excel Chart to Ignore Cells with Formulas Which Return #N/A

    Where is the chart that's causing the problem?
    Glenn



  6. #6
    Registered User
    Join Date
    04-30-2010
    Location
    washington dc
    MS-Off Ver
    Excel 2007
    Posts
    51

    Re: Excel Chart to Ignore Cells with Formulas Which Return #N/A

    Hi Glenn. This is the chart. I need the chart to graph cells EC26-EQ26 on the "Raw Data" tab.

    JacobChart Dynamic Range Example 2_6-15-2015.xlsx

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,858

    Re: Excel Chart to Ignore Cells with Formulas Which Return #N/A

    Sorry, I forgot about you!! I have reattached the sheet with a chart of the same type as Post 1. What's the problem that needs to be resolved?
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-30-2010
    Location
    washington dc
    MS-Off Ver
    Excel 2007
    Posts
    51

    Re: Excel Chart to Ignore Cells with Formulas Which Return #N/A

    Hi Glenn -

    So, the problem is that In the range EC26-EQ26, I need the chart to only chart the cells which return numerical values. The challenge being, on some occassions, it might be 6 cells. On other occassions, 10 cells, etc. etc. So I need to chart a dynamic range. I have been unable to, for some reason, set up a named range.

    I have tried using formulas using na() when the formula is false. This doesn't seem to work.

    In addition, I have tried to set a dynamic range, using the countif(range,">0"). Also does not seem to work.

    Jacob

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

    Re: Excel Chart to Ignore Cells with Formulas Which Return #N/A

    You need to add a dynamic named range, see Name Manager and the named range MYDATA.

    Then use that as the data values in the series of the chart.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    04-30-2010
    Location
    washington dc
    MS-Off Ver
    Excel 2007
    Posts
    51

    Re: Excel Chart to Ignore Cells with Formulas Which Return #N/A

    AH! Got it! I had left out the worksheet name when calling the dynamic range in the excel chart. Works perfect now. Thanks Glenn!

+ 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] Return non-blank cells from range AND ignore cells with only formula w/o results
    By grafitti in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-06-2014, 08:15 AM
  2. Replies: 1
    Last Post: 06-29-2012, 01:16 PM
  3. Telling excel to ignore blank cells in SUM formulas
    By rugbyfitz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-21-2008, 01:08 PM
  4. how do I get excel to ignore blank cells in formulas
    By rbignami in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-05-2005, 11:06 PM
  5. [SOLVED] how do you ignore blank cells in formulas
    By Kerry in forum Excel General
    Replies: 2
    Last Post: 02-16-2005, 10:42 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