Thread: ignoring null values in graph

I've built a data sheet to represent monthly result data that is appended to
each month. In the out-months a formula exists to prevent a DIV/0 error in
the data sheet. Problem is, the out months graph with a zero value. How do
I fix without having to update the chart range or formulas that calc the
percentages each month?

Take a look at Jon Peltier's Dynamic Chart information

http://www.peltiertech.com/Excel/Cha...html#DynColCht

Is this what you are looking for?

"Kris B" <Kris B@discussions.microsoft.com> wrote in message
news:1FC0C63B-61DD-4FC8-A907-D633DE626C6F@microsoft.com...
> I've built a data sheet to represent monthly result data that is appended
to
> each month. In the out-months a formula exists to prevent a DIV/0 error
in
> the data sheet. Problem is, the out months graph with a zero value. How
do
> I fix without having to update the chart range or formulas that calc the
> percentages each month?

If you don't want a dynamic chart per Barbara's suggestion, change your
formulas by replacing "" with NA(). What look like blanks ("") are
really text, which are evaluated by Excel as zero. NA() produces the
#N/A error in a sheet, which is certainly uglier than an empty cell but
can be hidden with conditional formatting, and it also is skipped over
by many common chart types.

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


Kris B wrote:

> I've built a data sheet to represent monthly result data that is appended to
> each month. In the out-months a formula exists to prevent a DIV/0 error in
> the data sheet. Problem is, the out months graph with a zero value. How do
> I fix without having to update the chart range or formulas that calc the
> percentages each month?