Plotting "blank" cells as zero
 

How can I keep an excel chart from plotting a blank cell as zero?

I am plotting a series of numbers, each month. If the month is not here yet,
then that cell is calculated as a blank. But the chart still plots it as
though it was zero.

Any ideas?

Thanks

Plotting "blank" cells as zero
 

I just wanted to add, that I have tried the NA() but this places "#NA" in the
cell, which defeats the purpose of leaving the cell blank. It will prevent
the cell from being plotted, but then the spread sheet is full of data that I
do not want to see. Is there another way?

"groj" wrote:

How can I keep an excel chart from plotting a blank cell as zero?

I am plotting a series of numbers, each month. If the month is not here yet,
then that cell is calculated as a blank. But the chart still plots it as
though it was zero.

Any ideas?

Thanks

Plotting "blank" cells as zero
 

Hi groj,

Format the cell font color to "white" and then custom format the number to
"[Black];[Red];[Black];[Blue]".

Wherever this style is used, positive numbers are black, negative nos are
red, zeroes are black, text is blue, and ANY error is white.

Works for #DIV0! #NA #VALUE!.

Note that this method was first suggested by Ture Magnusson on Excel-G.

Ed Ferrero
Microsoft Excel MVP
http://www.edferrero.com

I just wanted to add, that I have tried the NA() but this places "#NA" in
the
cell, which defeats the purpose of leaving the cell blank. It will prevent
the cell from being plotted, but then the spread sheet is full of data
that I
do not want to see. Is there another way?

"groj" wrote:

How can I keep an excel chart from plotting a blank cell as zero?

I am plotting a series of numbers, each month. If the month is not here
yet,
then that cell is calculated as a blank. But the chart still plots it as
though it was zero.

Any ideas?

Thanks

Plotting "blank" cells as zero
 

If the cell is really empty (i.e., does not have a formula that yields
""), select the chart, then Tools | Options... | Chart tab. In there
set the 'Plot empty cells as' appropriately.

If you have a formula: Another problem with NA() is that if the data
are used for downstream calculations, those calcs get messed up. But,
you can always create a linked copy of the data set and have the NA()s
in the copy. Plot the copy but use the original for printing / further
calculations. For example, if your data are in column B starting with
B2 and column C is empty, in C2 enter =IF(B2="",NA(),B2). Copy C2 as
far down C as needed. Now, plot C but use B for other work.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I just wanted to add, that I have tried the NA() but this places "#NA" in the
cell, which defeats the purpose of leaving the cell blank. It will prevent
the cell from being plotted, but then the spread sheet is full of data that I
do not want to see. Is there another way?

"groj" wrote:

How can I keep an excel chart from plotting a blank cell as zero?

I am plotting a series of numbers, each month. If the month is not here yet,
then that cell is calculated as a blank. But the chart still plots it as
though it was zero.

Any ideas?

Thanks