Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: getting blank cells to act like empty cells in line graphs

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've got sheets that automatically generate line graphs, but I've got a problem - when a cell contains an equation that blanks out the cell (e.g., =IF(A1="","",1)), what I'd like is for a point and attached line to that point not to be displayed for the point represented by that cell (like is the case if the cell is empty). Does anyone have any way to make the blank cell with the equation behave like an empty cell when it comes to graphing? I've tried TOOLS>OPTIONS>CHART>Active Chart>"Plot visible cells only", but that doesn't do the trick.

    Any help is appreciated.

    Dale

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    will a1="",0,1 do the trick?

  3. #3
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-08 23:03, dpolson wrote:
    I've got sheets that automatically generate line graphs, but I've got a problem - when a cell contains an equation that blanks out the cell (e.g., =IF(A1="","",1)), what I'd like is for a point and attached line to that point not to be displayed for the point represented by that cell (like is the case if the cell is empty). Does anyone have any way to make the blank cell with the equation behave like an empty cell when it comes to graphing? I've tried TOOLS>OPTIONS>CHART>Active Chart>"Plot visible cells only", but that doesn't do the trick.

    Any help is appreciated.

    Dale
    Try =IF(A1="",NA(),1) instead of
    =IF(A1="","",1)

    Eli

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi


    I think you will find many useful eaxmples here:
    http://www.ozgrid.com/Excel/ChartTips.htm

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =IF(ISNUMBER(A1);A1;NA())

    A1 can be a cell or formula.

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave, I use the NA() method frequently for line graphs but have been unable to Condition Format the cells so that they don't show #NA in the cell, do you know if and how it can be done.

    I have found one method which works though is not always practicable whereby I use a Combo Box to display a specific named range combined with an array formula which will display #NA as a blank cell. This is OK for some of the work I do but I would still like to do a Conditional Format for NA().

    Many Thanks if you can solve this for me.

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks all for the help. Using the NA() solved half of the problem - it got rid of the data point on the graph, but the graph line remains and runs through the spot of the missing datapoint. I'd also like to have it not display the line to/from the absent datapoint. Does anyone know a way to do this?

    Dale

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's what I've learned so far...

    These don't do the job at all:
    [1] =if(a1="","",1)
    [2] formula as in [1], then copy-paste special-values to another cell

    These do half of the job:
    [3] =if(a1="",NA(),1)
    [4] formula as in [3], then copy-paste special-values to another cell

    It seems that for the graph to not only not display the datapoint, but also not display the connecting line (which is the goal):

    [5] the data points have to be hand-entered into empty cells the hard way
    [6] or you do [1][2] or [3][4], then delete the blank cells to make them empty

    Three questions:

    Q1 - What does Excel leave behind in the blank cell after doing a copy-paste special-values of an equation that generates a "" in the original cell so that it's not really empty and thus doesn't graph like an empty cell?

    Q2 - Is there a way using functions & equations around this?

    Q3 - If not, is there a way using macros/VBA around this?

    I'd appreaciate anyones wisdom.

    Dale


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •