Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: XY (Scatter) chart issues.

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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).

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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).

  4. #4
    New Member
    Join Date
    Feb 2002
    Location
    M&T Bank
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    As Denise suggested... use a Line chart.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •