2D Chart - x scale label displays wrong

[Bill Carson]

Using Excel 2000, the chart displays systolic and diastolic blood
pressure as a line graph, with date of measurement on the x axis and
pressure on the y axis. There are 1049 values each for the systolic
and diastolic pressures.

If I allow the chart to automatically pick the maximum x axis date, it
doesn't do it right, so not all the data is plotted. When I force the
x axis scale to use the last date in the spreadsheet as the maximum,
then it plots all the data, but now the x axis labels are wrong, in
that they they are a half a year ahead of the dates in the
spreadsheet, i.e. the last date is 10/9/2007 in the spreadsheet, but
the last labeled x axis tic shows 3/4/08. When I open the "format
axis" dialog it shows a date of 4/1/2008, where I had earlier entered
the maximum date 10/9/2007, and checked the maximum control.

I'd rather not have to program Excel to fix this, because unless I am
entering something wrong in the dialog, Excel should be able to handle
this.

Thanks,

B.C.

 

[Jon Peltier]

How does it work if you plot the data on an XY chart instead?

- Jon

 

[Bill Carson]

NO - can't get it to work as an XY chart either. Is it possible I
have gone over the limit for a chart with 1050 values? Would badly
formatted data cause the problem? I have looked and looked at the data
and don't see anything. Is there any macro that will check data or
charts for errors?

I am wondering if I should update my Excel with the Microsoft updates
to see if it would make a difference? This is getting to be very
frustrating.

B.C.

 

[Jon Peltier]

A chart series can accommodate 32000 points, so that's not the problem. I
suspect your problem may be with bad data, such as non-numerical data
(numbers stored as text, etc.).

What I asked wasn't "does it work as an XY chart?", but "HOW does it work as
an XY chart?". Could you describe how it fails in an XY chart?

- Jon

 

[Bill Carson]

Sorry for being so slow to respond, but I am extremely busy on
other projects.

FYI, the dates for the chart are entered into the spreadsheet as:
10/9/2007 10:00 AM (as an example)
There is usually one measurement per day, and sometimes more,
of course at different times. The dates range from 5/19/2005 to
10/9/2007.

When I do an XY chart, and allow Excel to auto-pick the minimum
and maximum date ranges, it plots the y intercept as 1/0/1900 and
the last date on the axis as 4/14/1903.

If I enter the minimum and maximum dates in the format axis dialog
for the scale, then the y intercept is correct at 5/19/2005, but no
data
points or curves are plotted, i.e. the graph is blank.

Doe this give you any clues?

Thanks,

B.C.

 

[Jon Peltier]

1/0/1900 is zero and 4/14/1903 is 1200 when viewed in General number format.
Do you perhaps have 1100 or so points? If Excel does not recognize your X
values as numbers (or valid date-time values, which are also numeric), it
assigns X values of 1, 2, 3, etc.

Test the values. Make the column wider than needed to view the date-time
values, and change the horizontal alignment to General. Numbers are right
aligned and text is left aligned. If your dates are not right aligned, then
Excel will assign 1, 2, 3, as I described above. All you need is one bad
value, or one seemingly blank cell in the range that contains something like
a space character or other text.

- Jon

 

[Bill Carson]

EUREKA!!!!!

Thanks so much, I fixed it with your advice. I spread the date column
out and found two wrong entries, one had spaces in front of the date
and the other had a period instead of a colon in the time. After I
fixed it, the chart now displays as expected.

Thanks for sticking with me through this problem.

B.C.

 

[Jon Peltier]

It's always something dumb, isn't it?

- Jon

 

[Bill Carson]

Yes, garbage in and out. Which leads to a question
I thought of after I sent my last response. Will Excel
warn the user if he enters an incompatible format into
a column that is designated as date format? Apparently
not, since it allowed entry of bad date values in my
spreadsheet, in which I had set the format to be date
in the format dialog.

Do you know of any capability built into Excel to do
data entry warnings, or perhaps any code that will
parse entries and check for format errors?

Thanks,

B.C.

 

[Jon Peltier]

You could use data validation to try to limit what is entered, and
conditional formatting to try to highlight what doesn't belong.

- Jon

 

[Bill Carson]

Your forcing me to RTFM, but it was worth it. I now have applied data
validation to the date column and found it very useful. This should
reduce the bad data being entered.

Thanks again for all your help.

B.C.