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
ExcelPreferencesChart, 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

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
ExcelPreferencesChart, 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

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

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
ExcelPreferencesChart, 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