You are corect on the line/area just plummeting but it's a lot better then
showing a line at the bottom of the graph. Not to mention with a small
change to the code you can still graph the 0 values but remove the none 0
values.

=IF(ISERROR(VALUE(A1)>=0),-100,VALUE(A1))

This will allow you to graph the true 0 values but leave out the none 0
values.


"Andy Pope" wrote:

> Hi Mike,
>
> I can see how the actual point would not show when set to -100 and the
> minimum y axis value set to zero. But that does not stop the line
> between points plummeting to the base line and then re surfacing a bit
> further along.
> Or are your line charts just points?
>
> Cheers
> Andy
>
> Mike Punko wrote:
> > Ok got this to work for Line and bar graphs haven't tried anyothers yet.
> > The pesky problem of yoru graphs always plotting 0 or none 0 values
> > (#N/A...) as 0 can be hidden in a very sneeky way. Fish off in yoru chart
> > put a IF statement that if the value is greater then 0 everything goes as
> > normal, but if it's False then enter in a value like -100. Then on yoru
> > graph set your Y-axis Minimun to 0. the -100 Data value will be off the
> > graph and won't show up. Like I said very sneeky.
> >
> > If statement used.
> > =IF(A1>0,A1,-100)
>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
>