Charting cells with no values

[Adrian]

I have am using a chart which takes source data from a
group of cells. these cells have a formula along the lines
of:

=IF(A1"","",=A1*20)

When they return a value of "" (i.e. an empty cell) the
chart still treats the value as 0 and plots it on the
graph. I have made sure that the 'plot empty cells as'
option is set to 'Not plotted (leave gaps)'.

Completely empty cells are not plotted on the chart but
cells which contain the formula above and still remain
empty are still plotted on the chart as 0.

I want these cells to be ignored on the chart until the
data is available to populate them.

Can anyone help?

 

[Debra Dalgleish]

Change your formula to use NA() instead of an empty string:

=IF(A1="",NA(),A1*20)

 

[Jon Peltier]

The reason you need to use something like Debra's suggestion is that ""
is not an empty cell. It is a cell with a very short text string, and
Excel charts text as zero. Unfortunately Excel has no BLANK() or NULL()
worksheet function that can be displayed in this situation.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______