[SOLVED] Displaying null values in charts

I have made some charts with the data populating the underlying tables using
Vlookups. However as they are populated over time i dont want to show any
null values. I have tried the following formula =IF(D14=0,"",D14) to convert
the 0 values in to a null value but it still shows it on the chart. Do you
know how to get around this and if it is possible.

Use NA() instead of "".

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>, =?Utf-
8?B?SWFpbiBBU0xE?= <Iain [email protected]> says...
> I have made some charts with the data populating the underlying tables using
> Vlookups. However as they are populated over time i dont want to show any
> null values. I have tried the following formula =IF(D14=0,"",D14) to convert
> the 0 values in to a null value but it still shows it on the chart. Do you
> know how to get around this and if it is possible.
>

Functions must return something, and Excel has no null value.

Since your issue appears to be future values, Tushar's suggestion of
NA() should work just fine. If you needed connecting lines to break,
you would have to work much harder. The options there are to either

- delete the formula (possibly with a macro to re-load the formula as
data changes)

- use helper columns as described at
http://www.andypope.info/charts/brokenlines.htm

Jerry

Iain ASLD wrote:

> I have made some charts with the data populating the underlying tables using
> Vlookups. However as they are populated over time i dont want to show any
> null values. I have tried the following formula =IF(D14=0,"",D14) to convert
> the 0 values in to a null value but it still shows it on the chart. Do you
> know how to get around this and if it is possible.