+ Reply to Thread
Results 1 to 5 of 5

Graphs - Formula in source data outputs "" to override errors - ruining graphs

  1. #1
    Forum Contributor
    Join Date
    11-13-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    279

    Graphs - Formula in source data outputs "" to override errors - ruining graphs

    File attached...

    So My formula in the source data has an if function which outputs "" to override ugly na errors.

    Unfortunately this means my graphs plot zero data for these points when I would prefer they would just be blank, as is the case when it is left as an error functions.

    In the end I have to delete the formula in all the cells in order to make the graph look nice.

    BTW i am using Excel 2003 unfortunately,
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,134

    Re: Graphs - Formula in source data outputs "" to override errors - ruining graphs

    You could change the formula in C17 to this:

    =IF(C16="error",NA(),C16)

    Then you could hide the error using conditional formatting, setting the font colour to the same as the background colour.

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    11-13-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: Graphs - Formula in source data outputs "" to override errors - ruining graphs

    nice idea, but i should have said, the other problem is I am taking the median, which means if I don't remove the errors, i cant have the median, thanks

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,134

    Re: Graphs - Formula in source data outputs "" to override errors - ruining graphs

    Well, post the formula that you are using, and indicate where the errors are likely to occur.

    Pete

  5. #5
    Forum Contributor
    Join Date
    11-13-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: Graphs - Formula in source data outputs "" to override errors - ruining graphs

    The formula is pulling data from an external data source via an add-in that produces #n/a n/a whenever there is no data point.

    So for example it will give me:

    2007 2008 2009 2010 2011 2012
    #n/a n/a #n/a n/a #n/a n/a 197 201 503

+ 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. Creating "live" or dynamic graphs
    By mxracer86 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 11-10-2011, 07:40 PM
  2. Help with "Define Name" & auto-updating graphs
    By chrshrrsn in forum Excel General
    Replies: 5
    Last Post: 04-08-2011, 08:24 AM
  3. [SOLVED] Using "offset" functionality in graphs. HOW?
    By ZEB in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-24-2006, 02:55 PM
  4. I would like to set up two graphs that arin "synch".
    By Marc in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-02-2006, 02:10 PM
  5. making graphs ignore "blank " cells
    By Guillermo in forum Excel General
    Replies: 4
    Last Post: 02-22-2005, 06:06 PM

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