Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
how to add a second x axis with same Y axis value in one plot | Charts and Charting in Excel | |||
Secondary Axis Match Primary Axis Gridlines | Charts and Charting in Excel | |||
can't change how data on 2nd axis is being displayed | Charts and Charting in Excel | |||
Pivot Table to Ignore N/As | Excel Discussion (Misc queries) |