Stopping Charts Displaying Zero Results
 

I am building a set of templates to automate building charts and reporting
information. Is there a way to keep Excel charts from displaying a zero
value for future information, i.e. actual usage in March 2005, while
retaining the appropriate cell reference?

SCW,

Try one of these "fixes":

(1) Enter the formula "=NA()" for those cells that have zero values. The
X-Axes labels will be displayed but the series will not.
(2) Click on your chart and in the standard toolbar go to Tools - Options
- Chart - "Plot empty cells as" option.

----
Regards,
John Mansfield
http://www.pdbook.com

"SCW" wrote:

I am building a set of templates to automate building charts and reporting
information. Is there a way to keep Excel charts from displaying a zero
value for future information, i.e. actual usage in March 2005, while
retaining the appropriate cell reference?

I can't enter the "=NA()" formula into the cell because I already have a
formula to link to the master spreadsheet once we have entered an actual
figure spent. I tried plotting the empty cells as "not plotted (leave gaps)"
and it still insists on plotting a zero value on the chart. I'm trying to
make this report automatic, once we fill in the base data.

Thanks,

SCW

"John Mansfield" wrote:

SCW,

Try one of these "fixes":

(1) Enter the formula "=NA()" for those cells that have zero values. The
X-Axes labels will be displayed but the series will not.
(2) Click on your chart and in the standard toolbar go to Tools - Options
- Chart - "Plot empty cells as" option.

----
Regards,
John Mansfield
http://www.pdbook.com

"SCW" wrote:

I am building a set of templates to automate building charts and reporting
information. Is there a way to keep Excel charts from displaying a zero
value for future information, i.e. actual usage in March 2005, while
retaining the appropriate cell reference?

SCW,

If you already have formulas linking the master spreadsheet, would modifying
them with an IF statement to read something like this work:

=IF(SUM(A1:B1)<0,SUM(A1:B1),NA())

----
Regards,
John Mansfield
http://www.pdbook.com

"SCW" wrote:

I can't enter the "=NA()" formula into the cell because I already have a
formula to link to the master spreadsheet once we have entered an actual
figure spent. I tried plotting the empty cells as "not plotted (leave gaps)"
and it still insists on plotting a zero value on the chart. I'm trying to
make this report automatic, once we fill in the base data.

Thanks,

SCW

"John Mansfield" wrote:

SCW,

Try one of these "fixes":

(1) Enter the formula "=NA()" for those cells that have zero values. The
X-Axes labels will be displayed but the series will not.
(2) Click on your chart and in the standard toolbar go to Tools - Options
- Chart - "Plot empty cells as" option.

----
Regards,
John Mansfield
http://www.pdbook.com

"SCW" wrote:

I am building a set of templates to automate building charts and reporting
information. Is there a way to keep Excel charts from displaying a zero
value for future information, i.e. actual usage in March 2005, while
retaining the appropriate cell reference?

Thank you John. I think I'm brain dead today. It didn't even occur to me to
do an "if" statement.

SCW

"John Mansfield" wrote:

SCW,

If you already have formulas linking the master spreadsheet, would modifying
them with an IF statement to read something like this work:

=IF(SUM(A1:B1)<0,SUM(A1:B1),NA())

----
Regards,
John Mansfield
http://www.pdbook.com

"SCW" wrote:

I can't enter the "=NA()" formula into the cell because I already have a
formula to link to the master spreadsheet once we have entered an actual
figure spent. I tried plotting the empty cells as "not plotted (leave gaps)"
and it still insists on plotting a zero value on the chart. I'm trying to
make this report automatic, once we fill in the base data.

Thanks,

SCW

"John Mansfield" wrote:

SCW,

Try one of these "fixes":

(1) Enter the formula "=NA()" for those cells that have zero values. The
X-Axes labels will be displayed but the series will not.
(2) Click on your chart and in the standard toolbar go to Tools - Options
- Chart - "Plot empty cells as" option.

----
Regards,
John Mansfield
http://www.pdbook.com

"SCW" wrote:

I am building a set of templates to automate building charts and reporting
information. Is there a way to keep Excel charts from displaying a zero
value for future information, i.e. actual usage in March 2005, while
retaining the appropriate cell reference?