Thanks - looing through JPs stuff I can't find a solution - it applies to
blank cells in the middle of data not at the end.
My blanks at the end are necessary as the user selects a product then the
data and graph update automatically. As the amount of data changes I need a
formula that returns either data or "" (or something else that the graph
cannot plot).
I'm using a line chart.
I have a list of data which contains a string of NA#s at the end. When I
plot it the graph doesnt plot the NA# but it shifts the line to the left as
if it was plotted. What I would like is the last point of the graph to be to
the very right of the graph to use all the space. If I replace the NA() with
"" i get the same result. If I delete the cell completely the graph moves to
the right as it should.
I'm using dynamic ranges.
Thanks
LiAD
"Luke M" wrote:
> What type of chart are you using? Jon Peltier provides several examples of
> how you can handle gaps, and how different chart types vary at:
> http://peltiertech.com/WordPress/min...g-empty-cells/
>
> --
> Best Regards,
>
> Luke M
> "LiAD" <(E-Mail Removed)> wrote in message
> news:25ACFA09-ED43-4C91-98B1-(E-Mail Removed)...
> > Hi,
> >
> > Thanks for the suggestion.
> >
> > I'm afraid the chart engine (or at least mine anyway) doesn't ignore it -
> > it
> > plots nothing on the graph but shows a lot of empty space which
> > increases/decreases as i add/delete N/A's.
> >
> > If i try this technique on a chart without dynamic ranges it doesnt work
> > either.
> >
> > Any ideas why this would be different to your result?
> >
> > Thanks
> >
> > "Bernard Liengme" wrote:
> >
> >> Replace the blank by #N/A
> >> So let's say your formula is =IF(this-test, B2,"") then use
> >> =IF(this-test,B2,NA())
> >> When the test fails, this displays #N/A which the chart engine ignores
> >> If this looks odd in a print out, use a conditional format such as
> >> =ISNA(C2)
> >> and make the font the same as the cell background - make it invisible on
> >> the
> >> screen and then printed.
> >> best wishes
> >> --
> >> Bernard Liengme
> >> Microsoft Excel MVP
> >> http://people.stfx.ca/bliengme
> >>
> >> "LiAD" <(E-Mail Removed)> wrote in message
> >> news:FB1B18B9-BDF6-4B19-884F-(E-Mail Removed)...
> >> > Hi,
> >> >
> >> > In a chart I have a formula that either returns a number or "" in a
> >> > cell.
> >> > I
> >> > then have a dynamic range set which plots the values on a graph. The
> >> > problem
> >> > is that the chart will plot the ""s as zeros as it sees something in
> >> > the
> >> > cell.
> >> >
> >> > Is there a way I can have a true blank cell result coming from a
> >> > formula?
> >> >
> >> > If not how I can avoid the dynamic range seeing this as a zero?
> >> >
> >> > Thanks
> >> > LiAD
> >>
> >> .
> >>
>
>
> .
>
Similar Threads
