plotting cells that contain ""

[mikelee]

Hello,
I have a table that has formulas such as the following in
it:

=IF($L21>NewStats!$B$47,"",SUM(B$8:B21))

The table keeps a running total based on individual
entries in another table. If there aren't any entries in
the first table, then the corresponding cell in the
running total table is blank (or, is filled with "").

I'm trying to plot the running totals, but the "empty"
cells ("") are plotting as zeros. I've gone to
Tools/Options/Chart and selected "Not Plotted"
under "Plot Empty Cells As." It doesn't appear that it
veiws a cell with "" as the result of the function
as "empty." I've also tried changing the formula as
follows:

=IF($L21>NewStats!$B$47,NULL,SUM(B$8:B21))
=IF($L21>NewStats!$B$47,,SUM(B$8:B21))

neither of which was successful.

My questions; any advice on something I can use in the
formula besides "" to make the chart realize that the
cell is "empty?" Any other settings anywhere that I can
change to alter the way the chart handles "" cells?

Thanks to all for the help.

Mike

 

[Frank Kabel]

Hi
try:
=IF($L21>NewStats!$B$47,NA(),SUM(B$8:B21))

 

[mikelee]

Frank,
That did it. It makes the table look kind of ugly, but
with the chart working, noone but me has to see the
table

Thanks for the help.

Mike

 

[Frank Kabel]

Hi
to make the table more viewable try the following:
- select the cells (lets say you have the #NA values in cells B1:B10)
- goto 'Format - Conditional Format'
- enter the following formula
=ISNA(B1)
- choose a white font for these cells