Please someone save my sanity

  • Thread starter Thread starter Gromit
  • Start date Start date
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
 
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
 
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
 
Back
Top