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"

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?

 

[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"

 

[Peter Bernadyne]

Thank you for enlightening me on this.

Regards,

-Peter