+ Reply to Thread
Results 1 to 4 of 4

blank cells plot as zero values

  1. #1
    Pete Nelson
    Guest

    blank cells plot as zero values

    This is a problem that's been discussed before, but I haven't seen this
    aspect addressed: If you select the chart in question and go to
    Excel>Preferences>Chart, you can indicate that empty cells should be
    plotted as 'Not plotted (leave gaps)". (This is one of 3 options
    including "Zero" and "Interpolated".) Thing is, this doesn't work!

    As far as I'm able to determine, the ONLY thing that Excel will do is
    treat your blank cells as zero values (yeah, yeah, unless I replace
    those blank cells with "#N/A"). The work-arounds are fine, but why
    won't Excel do what it's supposed to? Any chance they'll fix this?

    Thanks, Pete

    Microsoft Excel 2004 for Mac, ver 11.2.3


  2. #2

    Re: blank cells plot as zero values

    I have gone through a lot of hoops, and lost days trying to get around
    this. In one instance, I discovered that the empty cells that were
    plotting as zero were nulls from a paste from an Access database.
    Interrestingly, one or two non-adjacent nulls would work ok sometimes
    (lines on a chart would interpolate properly rather than the lines
    being segmented). After a little time building ugly queries, I
    discovered by accident that "Paste Special"/text creates empty
    cells("") rather than nulls. All the ugly queries are now in the
    trash; I just have to remember to always use the Paste-Special/text
    means of pasting.

    I wish there were some way to show the actual nature of an apparently
    empty cell.

    Hoe this helps,
    Jim



    All the empty cells that ploted as zero were nulls pasted from Access.
    Pete Nelson wrote:
    > This is a problem that's been discussed before, but I haven't seen this
    > aspect addressed: If you select the chart in question and go to
    > Excel>Preferences>Chart, you can indicate that empty cells should be
    > plotted as 'Not plotted (leave gaps)". (This is one of 3 options
    > including "Zero" and "Interpolated".) Thing is, this doesn't work!
    >
    > As far as I'm able to determine, the ONLY thing that Excel will do is
    > treat your blank cells as zero values (yeah, yeah, unless I replace
    > those blank cells with "#N/A"). The work-arounds are fine, but why
    > won't Excel do what it's supposed to? Any chance they'll fix this?
    >
    > Thanks, Pete
    >
    > Microsoft Excel 2004 for Mac, ver 11.2.3



  3. #3
    Pete Nelson
    Guest

    Re: blank cells plot as zero values

    Jim: I think you're on to something. I was certain that what'd provided
    wasn't relevant until I input the data for a very simple chart
    (months:values for each month with a couple missing values). To my
    surprise, the chart ignored the blank cells, as it should have. When I
    went into the Preferences, I was able to treat the blanks as zeros or
    interpolate between adjacent values. The blanks in the data that
    prompted this post were the "" output from a formula. While I still
    think this is a bug that should be fixed, at least Excel will sometimes
    perform as it's supposed to perform...

    Thanks, Pete

    [email protected] wrote:
    > I have gone through a lot of hoops, and lost days trying to get around
    > this. In one instance, I discovered that the empty cells that were
    > plotting as zero were nulls from a paste from an Access database.
    > Interrestingly, one or two non-adjacent nulls would work ok sometimes
    > (lines on a chart would interpolate properly rather than the lines
    > being segmented). After a little time building ugly queries, I
    > discovered by accident that "Paste Special"/text creates empty
    > cells("") rather than nulls. All the ugly queries are now in the
    > trash; I just have to remember to always use the Paste-Special/text
    > means of pasting.
    >
    > I wish there were some way to show the actual nature of an apparently
    > empty cell.
    >
    > Hoe this helps,
    > Jim
    >
    >
    >
    > All the empty cells that ploted as zero were nulls pasted from Access.
    > Pete Nelson wrote:
    > > This is a problem that's been discussed before, but I haven't seen this
    > > aspect addressed: If you select the chart in question and go to
    > > Excel>Preferences>Chart, you can indicate that empty cells should be
    > > plotted as 'Not plotted (leave gaps)". (This is one of 3 options
    > > including "Zero" and "Interpolated".) Thing is, this doesn't work!
    > >
    > > As far as I'm able to determine, the ONLY thing that Excel will do is
    > > treat your blank cells as zero values (yeah, yeah, unless I replace
    > > those blank cells with "#N/A"). The work-arounds are fine, but why
    > > won't Excel do what it's supposed to? Any chance they'll fix this?
    > >
    > > Thanks, Pete
    > >
    > > Microsoft Excel 2004 for Mac, ver 11.2.3



  4. #4
    Pete Nelson
    Guest

    Re: blank cells plot as zero values

    Jim: I think you're on to something. I was certain that what'd provided
    wasn't relevant until I input the data for a very simple chart
    (months:values for each month with a couple missing values). To my
    surprise, the chart ignored the blank cells, as it should have. When I
    went into the Preferences, I was able to treat the blanks as zeros or
    interpolate between adjacent values. The blanks in the data that
    prompted this post were the "" output from a formula. While I still
    think this is a bug that should be fixed, at least Excel will sometimes
    perform as it's supposed to perform...

    Thanks, Pete

    [email protected] wrote:
    > I have gone through a lot of hoops, and lost days trying to get around
    > this. In one instance, I discovered that the empty cells that were
    > plotting as zero were nulls from a paste from an Access database.
    > Interrestingly, one or two non-adjacent nulls would work ok sometimes
    > (lines on a chart would interpolate properly rather than the lines
    > being segmented). After a little time building ugly queries, I
    > discovered by accident that "Paste Special"/text creates empty
    > cells("") rather than nulls. All the ugly queries are now in the
    > trash; I just have to remember to always use the Paste-Special/text
    > means of pasting.
    >
    > I wish there were some way to show the actual nature of an apparently
    > empty cell.
    >
    > Hoe this helps,
    > Jim
    >
    >
    >
    > All the empty cells that ploted as zero were nulls pasted from Access.
    > Pete Nelson wrote:
    > > This is a problem that's been discussed before, but I haven't seen this
    > > aspect addressed: If you select the chart in question and go to
    > > Excel>Preferences>Chart, you can indicate that empty cells should be
    > > plotted as 'Not plotted (leave gaps)". (This is one of 3 options
    > > including "Zero" and "Interpolated".) Thing is, this doesn't work!
    > >
    > > As far as I'm able to determine, the ONLY thing that Excel will do is
    > > treat your blank cells as zero values (yeah, yeah, unless I replace
    > > those blank cells with "#N/A"). The work-arounds are fine, but why
    > > won't Excel do what it's supposed to? Any chance they'll fix this?
    > >
    > > Thanks, Pete
    > >
    > > Microsoft Excel 2004 for Mac, ver 11.2.3



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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