Please someone save my sanity

G

Gromit

Hi,

I have a matrix as below that I want to chart using a line chart.
want the empty cells to be ignored by the chart, so I can use the Hi-L
bar option of the line chart type.

X Y Z
a 1 5
b 3 5
c 2
d 5 5
e 5
f 5 5

Unfortunately, the 'empty' cells are not really empty; they contain
formula that returns an empty string:

(e.g. =IF(LEN(B2)=0,"",B2).

As a result, my chart is plotting these 'empty' cells as zeros, so th
Hi-Lo bars are all dropping to zero. Anyone know how I can trick th
chart into thinking the cells really are empty?

Any help much appreciated, this is driving me nuts!!!

Thanks,

Graha
 
G

Gromit

Hi,

That matrix didn't come through very clearly in my last post. Let's try
this...

_ X Y Z
a 1 _ 5
b _ 3 5
c 2 _ _
d 5 _ 5
e _ 5 _
f 5 _ 5

Graham
 
B

Biff

Hi Graham,

Replace the null sting in your formula with NA()-

=IF(LEN(B2)=0,NA(),B2)

This will show #N/A errors in the cells but you can hide
those with conditional formatting.

=ISNA(B2) and set the text color to the same as the
background color.

Biff
 
Top