XY (Scatter) chart issues.

saydan

New Member
Joined
Feb 19, 2002
Messages
6
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...
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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).
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top