Chart incorrectly displays time of day on x-axis

[SPH1967]

I have a problem when trying to display a graph of time vs. value.

If the number of data elements is less than or equal to 2665, the graph
displays correctly. The data is centered on the x-axis.

However, if I try to display more than 2665 elements, the x-axis on the
graph forces itself to display a full 24 hours (12AM to 12AM) even though my
data is from around 630PM to 1012PM. The chart then looks strange and is
difficult to read.

To duplicate this, simply create two columns of data:

6:30:00 PM 1
6:30:05 PM 2
6:30:10 PM 3
6:30:15 PM 4
6:30:20 PM 5
6:30:25 PM 6
....
10:11:50 PM 2663
10:11:55 PM 2664
10:12:00 PM 2665


Extend this all the way down to row 2665. Create an XY scatter graph of all
data from row 1 to row 3000. The chart displays the x-axis correctly (from
6pm to 1048pm) and centers the data nicely. Add one more row of data (on row
2666) and the chart then gets screwed up.

Any ideas what might be causing this?

Thanks in advance.

 

[Shane Devenshire]

Hi,

Just lock the Max and Min values for the X-axis scale:

2003: Right-click the axis and choose Format Axis, Scale and uncheck the
Auto beside Maximum and Minimum.

same idea, slightly different location in 2007.

 

[SPH1967]

Thanks, Shane. However, the time range and data will be variable and I want
the chart to automatically adjust each time.

Can you duplicate this in 2007? I'm using 2003.

thx

 

[Jon Peltier]

You've run into an arbitrary constant that's been designed into Excel.
According to "How Chart Axis Limits Are Determined"
(http://support.microsoft.com/kb/214075):

"If the difference between yMax and yMin is greater than 16.667 percent of
the value of yMax, the automatic maximum for the y-axis is zero."

This means yMin / yMax should be greater than 5/6 (0.833333333) or Excel
will use 0 for yMin

6:30:00 pm / 10:12:00 pm = 0.770833333333 / 0.925000000000 = 0.833333333333

Phew, barely makes it.

6:30:00 pm / 10:12:05 pm = 0.770833333333 / 0.925057870370 = 0.833281201126

Oops, his doesn't make it.

- Jon