Share via

Scatter Plots Multiple Series

Anonymous
2012-12-10T21:00:45+00:00

I have ten sets or groups of data that I am attempting to plot using an xy scatter plot with lines.  The x axis is date data and the y axis is a numerical result.  The date data is sorted ascending for each set or group.  If I plot one set, everything is fine.  If I add another series (set) the x axis defaults to 1/0/1900  - 11/21/2036.  I can't find how to stop this or set the x axis range to the proper scale.  This used to be easy.

Date Collected Result
A 10/11/1994 0.26
A 10/17/1996 0.28
A 6/26/1997 0.3
A 3/1/2000 0.3
A 3/10/2003 0.304
A 1/17/2006 0.285
A 2/6/2009 0.321
A 9/6/2012 3.6
A 9/26/2012 0.026
B 7/7/1993 0.4
B 9/10/1997 0.42
B 12/27/2000 0.4
B 5/7/2003 0.43
B 7/26/2006 0.41
B 8/26/2009 0.4
B 9/5/2012 2.59
B 11/5/2012 0.389

Good Chart

Bad, BAAAAAAAD, Chart

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-12-14T18:01:48+00:00

    Sorry, didn't work.  I tried this by creating a new chart with one series.  When one adds another series, Excel adjusts the x-axis range to 1/0/1900 - 11/21/2036 before one even starts selecting a range for the x-axis.

    I had initially tried to define new columns as date and number respectively, then cut and paste special as values, as one often had to do in O2003.  Didn't work.

    I did the same with creating a worksheet with the data types predefined, I even manually typed in a couple of sets in columns with data types predefined.  This didn't work either.

    I still got the same effect.

    I brought in a VB routine I wrote in O2003 to produce over a 1,000 publication ready charts and tables and it was a horrid mess in O2007.

    The other really annoying thing about this is when one uses the fixed range method, converting to a serial number and substituting in the x-axis range doesn't work well.  So I do not know what they are doing.   One can use the formula : DATE(1900+INT("DATE TO CONVERT"/1000),1,MOD("DATE TO CONVERT",1000)) in a cell defined as number to get the equivalent serial number for the date.

    So for 7/7/1993 (my lowest test date) the corresponding serial number is 12576.  This equated to 6/6/1934 for a minimum date when used on the fixed scale.  So apparently I don't understand how they are representing and calculating dates any more.

    I ended up using 33500.0 to set a fixed minimum x-axis value after substituting in numbers manually.  That is absolutely asinine, effective, but still asinine.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-12-14T17:22:59+00:00

    This is what I ended up doing.  Seems asinine to have to play and guess at max/min values though.  This worked fine in Office 2003 and prior. I used to do hundreds of multi-series charts and have dozens of VB routines to do grouping and custom plotting.  I can't believe they mucked this in Office 2007.

    Seems to me the developers know little about charting.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-12-10T21:37:55+00:00

    Try this.  Select the cells containing the dates, then do Data > Text to Columns > Fixed width > Next > Next > Date > MDY > Next > Finish.

    Does that fix it?

    Cheers

    Rich

    Was this answer helpful?

    0 comments
  4. Anonymous
    2012-12-10T21:31:37+00:00

    > I can't find how to ... set the x axis range to the proper scale. <

    One way is to right-click the horizontal axis, choose Format Axis > Axis Options, and enter Fixed values for the Minimum and Maximum.  I used 34000 and 42000 for your data.

    -  Mike Middleton, www.MikeMiddleton.com, www.TreePlan.com

    Was this answer helpful?

    0 comments