Dynamic Charts and Blanks

[Guest]

Dear All.

I have produced a chart dynamically, using the technique outlined on the
Peliter website.

In general this works fine, in terms of when the list of data grows.
However, when my data list shrinks it causes a problem.

The data is the result of an Offset function, which leaves a blank cell
where there is no data. However, Excel charts a zero value - which I dont
want to have (I would rather it treat the cells as though they were blank).

Does anyone know how to do this???

Thanks.

 

[Jon Peltier]

You need to change your formula so it results in NA() instead of "" (which
isn't blank, by the way, it's a short text value). This looks like #N/A in
the sheet, but isn't plotted in a line or XY chart. Use conditional
formatting to hide the error:

http://contextures.com/xlCondFormat03.html#Errors

- Jon

 

[Guest]

Thanks.

How do I change my formula to get an error??

My current formula is:

=OFFSET($BL$1,MATCH(ROW()-1,$BL$2:$BL$17,0),-2)

 

[Jon Peltier]

I'm guessing that it might be like this, though I'm not sure I understand
why it doesn't already raise an error:

=if(isna(MATCH(ROW()-1,$BL$2:$BL$17,0)),NA(),OFFSET($BL$1,MATCH(ROW()-1,$BL$2:$BL$17,0),-2))

- Jon