Solved: Date Problem In Chart

[U_Shrestha]

Hi all,

I have 2 problems in my attached chart:

a) Chart displays double dates for each comparative data, e.g. instead of showing Apr-09 only once, it is showing Apr-09 Apr-09 in the chart.

b) My data table sometimes has text "NS" instead of a number. NS meaning "Not Sampled during that particular month". Currently it shows zero; instead of showing zero is there any way I can represent it in the chart or should I just remove the corresponding data column to avoid that error.

Your advice would be very helpful and appreciated. Thank you.

[mbarron]

for a):
Change your X Axis from auto plotting on Days to Months and adjust you Major and Minor unit as well.

[p45cal]

for (b)
Blank cells are not plotted (actually you have the choice of how to plot them; leave gaps, plot as 0 or draw a line direclty from previous to the next point). Hidden columns/rows are not plotted (although this can be overridden).
Cells with the error #N/A aren't plotted but you have no choice, a line is drawn directly from points either side.
If you have only a few such points, you could edit inividual data points to show no line and no marker. Hard work.

So it depends how much you are prepared to alter your data.
You could create parallel series with formulae such as
=IF(ISNUMBER(G4),G4,NA())
and plot those instead, but you'd still have the points either side of NS connected with a line on the chart.
(=IF(ISNUMBER(G4),G4,"") didn't work! (well the formula did, but the points were still plotted as 0).)

Hiding columns with NS in them is a possibility but you're not guaranteed to have both values being NS for different series being in the same columns that you're plotting on the same graph.

I found the most straightforward way was to replace NS with =NA() or blank (deleted), wholesale. In order not to be too destructive and to save work putting data back as it was, I selected only the data that might be plotted (C3:R4,C7:R8,C11:R12,C15:R16,C19:R20) and typed in a Name in the Name box to give it a Name. Then I selected that Named range using the name box dropdown and searched and replaced NS with either =NA() or just deleted them. I made sure the options in the chart were as I wanted. QED.

Replacing the NS values in the data was easy as, again, a search and replace while the Name was selected made sure I wasn't putting NS into cells that didn't have NS in before.

[U_Shrestha]

mbarron: how do I do that? I was able to find Format Axis/Scale but I don't know how to change the other data. Now, is this the only place where I need to do adjustment? Thanks.

(I am working on part b)

[U_Shrestha]

hi p45cal,

I used the =NA() formula and I am very much satisfied with the results. Thanks so much for the tip

[p45cal]

mbarron: how do I do that? I was able to find Format Axis/Scale but I don't know how to change the other data. Now, is this the only place where I need to do adjustment? Thanks.

(I am working on part b)

right-click the x-axis, choose format axis, change base unit to Months and Major Unit to 1 Months. OK. Finished.

[U_Shrestha]

Hi p45cal,

Thanks for the tip, that did the trick! You have been very helpful for my project. Thank you