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

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


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

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

"Peter Bernadyne"
<Peter.Bernadyne.27wpwn_1147794005.5064@excelfor um-nospam.com wrote in
message news:Peter.Bernadyne.27wpwn_1147794005.5064@excelf orum-nospam.com...

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

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

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

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

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.27yoyy_1147886102.7013@excelfor um-nospam.com wrote in
message news:Peter.Bernadyne.27yoyy_1147886102.7013@excelf orum-nospam.com...

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

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

Thank you for enlightening me on this.

Regards,

-Peter


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