How to Ignore #N/As for X-Axis TickMarks?

[Peter Bernadyne]

I have a time-series line chart I am plotting at changing frequencies depending upon user input. Under the Axes option, I am using 'Category', so as to reflect the actual dates.

At the highest frequency (daily), I have a date for every datapoint, so I have no problem here. However, at any other lower frequency, while the chart correctly ignores interim datapoints whose values are #N/A (rather than plotting zero), for some reason the x-axis does NOT ignore these and therefore I cannot accurately display the dates corresponding to the valid datapoints at these other frequencies, returning mostly #N/A tickmarks along the x-axis.

Does anyone happen to know of a workaround solution to this?

Any advice would be gratefully received.

-Pete

[David Biddulph]

"Peter Bernadyne"
<[email protected]> wrote in
message news:[email protected]...
>
> I have a time-series line chart I am plotting at changing frequencies
> depending upon user input. Under the Axes option, I am using
> 'Category', so as to reflect the actual dates.
>
> At the highest frequency (daily), I have a date for every datapoint, so
> I have no problem here. However, at any other lower frequency, while
> the chart correctly ignores interim datapoints whose values are #N/A
> (rather than plotting zero), for some reason the x-axis does NOT ignore
> these and therefore I cannot accurately display the dates corresponding
> to the valid datapoints at these other frequencies, returning mostly
> #N/A tickmarks along the x-axis.
>
> Does anyone happen to know of a workaround solution to this?

Have you tried an X-Y scatter chart instead of a line chart?
--
David Biddulph

[Peter Bernadyne]

Hi,

Thank your for this kind suggestion.

To answer your question, I have indeed tried it and it does work around this problem.

However, I have spotted a fault in my original posting. I neglected to mention that I also have a column chart version of this chart which, unlike the scatter-plot solution, does not appear to work as the chart still reads the #N/A datapoints and my columns become very very narrow (or thin) as the representation of yearly datapoints occur only a very few times compared to the many hundreds of daily observations I have.

That's why I was wondering whether there was some workaround to get the chart to ignore #N/A values along the axis entirely. Seems strange that #N/As are not plotted, yet still show up on the axis.

Any help much appreciated.

-Pete

[Jon Peltier]

The NA() doesn't work to skip points with all kinds of charts, just XY,
line, and unfilled radar charts. Also, the NA() is bypassed only along a
value axis, so both axes on an XY chart (i.e., X and Y values) and a line
chart with a time-scale axis, but only Y on a line or unfilled radar chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


"Peter Bernadyne"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> Thank your for this kind suggestion.
>
> To answer your question, I have indeed tried it and it does work around
> this problem.
>
> However, I have spotted a fault in my original posting. I neglected to
> mention that I also have a column chart version of this chart which,
> unlike the scatter-plot solution, does not appear to work as the chart
> still reads the #N/A datapoints and my columns become very very narrow
> (or thin) as the representation of yearly datapoints occur only a very
> few times compared to the many hundreds of daily observations I have.
>
> That's why I was wondering whether there was some workaround to get the
> chart to ignore #N/A values along the axis entirely. Seems strange that
> #N/As are not plotted, yet still show up on the axis.
>
> Any help much appreciated.
>
> -Pete
>
>
> --
> Peter Bernadyne
> ------------------------------------------------------------------------
> Peter Bernadyne's Profile:
> http://www.excelforum.com/member.php...fo&userid=7017
> View this thread: http://www.excelforum.com/showthread...hreadid=542554
>

[Peter Bernadyne]

Thank you for enlightening me on this.

Regards,

-Peter