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

Thread: having problems getting line graphs to ignore blank cells

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

    Default

    I'm trying to find a way to get Excel line graphs to ignore blank cells.

    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

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

    Default

    I don't have an answer, but I'd like to vehemently agree that this is indeed a frustrating problem.

    I tried several of the same methods you did, including N/A, paste-special, etc. I found that similar to your attempts, I could do it only after a couple manipulations, like using Goto to select all cells with "" or #N/A in them that should be blank, and then hitting delete to clear them all at once (saves the time of doing it one at a time). But there really should be a way of working it into the spreadsheet from the start...

    Clearly the graph sees the formula in the cell and concludes, "Well, if there's a formula in the cell it's obviously not BLANK"

    Hopefully someone else can help...

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
  •