XY (Scatter) chart issues.

[saydan]

I was wondering if anyone else has faced this problem and has come up with a solution.

I created a XY Scatter chart in Excel 97 and the X axis is the date, and the Y is the temperature. The min/max for the X axis is set to auto select but the Y min is 1/1/1979 and the max is 1/1/2003. The major unit is 730.5 and the minor unit is 365.25. This should display everyother year on the X axis, but instead it shows 79,80,83,84, and so on. Is there away around this problem but by still us the same date values?
Thanks,
dan...

[Anne Troy]

Did you right-click the X axis and choose format, then set the major unit to 365? If you're using actual DAY dates, you may want to just show the month/year or just year (use formatting on the column that represents the years).

[saydan]

yes I clicked on the X axis and set the major unit to 365.25 and it gives me the the incorrect years along the bottom. When I set the minimum value to 1/1/1978 it displays it correctly. It just doesn't display it correctly if the data is 1/1/1979.
The date values it is ready in is formatted to only display the year (yyyy).

[Denise]

Three things:
1- I don't think there is any reason to use an XY graph in this case. Try line graph. It may help if excel read the dates as labels.

2- There is an option that allows you to specify units on the x axis.

3- I've had graphs that displayed every other month and every other year because the graph area simply wasn't large enough. Reducing the font size for the x axis also helps this sometimes.

Good luck,
Denise

[saydan]

No this isn't the problem at all.
I can mak
The problem is the when the starting date is
1/1/1979 and the ending date is 1/1/2003 it will plot the values incorrectly. However if I change the starting date (and only that ) to 1/1/1978 it will display the data correctly.

[Mark W.]

Don't you think this might have something to do with the fact that 1980 is a leap year, and 1 day is added on a leap year rather than 0.25 day each year. So if you start with an odd numbered year your minor and major units are out of phase.


[ This Message was edited by: Mark W. on 2002-02-21 15:06 ]

[saydan]

I also thought it was because the years were odd numbers but then I set the starting year to 1980 then I get the same problem, and if I set the starting the date to 1981 it is ok.

[Mark W.]

Okay, okay... I shouldn't have mentioned "odd" years in my response. The point is: According to the Gregorian calendar each year isn't 365.25 days long. Let's create a quick model to explain what's happening...

Enter 1/1/78 into A1 and create a year-based series ending in cell A12 with 1/1/89 [I used =EDATE(A1,12)]. Next, enter the formula, =A1+365.25, into B2, the formula, =B2+365.25, into B3 and copy down to B12. Finally, enter the formula, =TRUNC(B2)=A2, into C2 and copy down to C12. Notice, that the column B value which should be predictive of the column A value is wrong (column C is FALSE) every 4 years. (I applied a color pattern to the odd numbered rows to help me identify the alternating years that would be graphed.) Furthermore, notice that with 1978 as the base year it's wrong in 1981, 1985, etc., and that these years aren't displayed on your XY Chart using 1978 as its base. The discrepancies are there, but you're not displaying them!!! If you begin with 1979 the XY Chart displays the discrepancies. It's a "phasing thing"! Does this make sense?


[ This Message was edited by: Mark W. on 2002-02-21 16:22 ]

[saydan]

So what would be teh best way to over come this problem.
Is there a way to get my X axis to show every other year correctly all the time no matter what the starting year is?

[Mark W.]

As Denise suggested... use a Line chart.