Scatter Plot Scales
Scatter Plot Scales
(OP)
Hello all.
Using Excel 2010, version 14.0.7015.1000
Does anyone know if you can fix the horizontal scale on a scatter plot so that dates appear on a fixed pattern? I have many years of data and I would like the plots to have 1/1/xxxx for the labels. I can get this to work if the data only spans a couple of years by setting the major unit to 365.25, but as the number of years increases this stops working.
Same general question applies to trying to get the first day of the month to appear on the scale.
Anybody found a solution to this?
Using Excel 2010, version 14.0.7015.1000
Does anyone know if you can fix the horizontal scale on a scatter plot so that dates appear on a fixed pattern? I have many years of data and I would like the plots to have 1/1/xxxx for the labels. I can get this to work if the data only spans a couple of years by setting the major unit to 365.25, but as the number of years increases this stops working.
Same general question applies to trying to get the first day of the month to appear on the scale.
Anybody found a solution to this?
Mike Lambert





RE: Scatter Plot Scales
Use a LINE Chart with Markers.
Set the Line to No Line.
Format the x-axis Type as Date Axis.
Select Units as Years.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Scatter Plot Scales
I had tried using a line plot several years (versions) ago and that would not maintain the relative position of the data. I see that particular issue has been fixed.
Thanks again.
mike
Mike Lambert
RE: Scatter Plot Scales
So the line graph works fine if all of you data has the same dates. However, I you try to add data that has a different date range, it does not plot correctly.
Here is a quick example of the problem I'm seeing with made up data. As you can see, series 2 is not plotting at the correct location relative to the x (date) axis.
Any ideas?
Mike Lambert
RE: Scatter Plot Scales
You implicitly assigned your second table data DATEs to the secondary axis OR your x-axis implicitly changed from a NUMBER axis, which would include DATE, to a CATEGORY axis, which is simply point by point, rather than numerically proportional.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Scatter Plot Scales
Part of the problem is that you are using a truncation of the number of days of the year, i.e., a year is actually more like 365.2422 days. However, there may be a simpler way. Since you know the exact mapping of the first day of each month to the timestamp that Excel uses, you can create a table of month lengths that you can use to normalize their actual lengths to a uniform spacing. With some brute-forcing, you could remap the time stamps to look like:
1/1/2000 200008.333
1/1/2001 200108.333
1/1/2005 200508.333
1/1/2002 200208.333
1/1/2004 200408.333
8.333 is 1/12th of 100
TTFN
I can do absolutely anything. I'm an expert!
homework forum: //www.engineering.com/AskForum/aff/32.aspx
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers
RE: Scatter Plot Scales
In the data I posted there is no data for 2003, yet I have 1/1/2003 on the x-axis with no y-axis value obvoiusly.
The "trick" is to change the x-axis to DATE and the UNIT as YEAR. Solves the 365.25 problem as well.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Scatter Plot Scales
Actually (at least in XL 2016) if the X axis data is date numbers the chart automatically displays in date format, although if you want to set the label spacing you have to change the axis type from Automatic to Date.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Scatter Plot Scales
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Scatter Plot Scales
In 2016 I had to:
Select the chart
Select the Design ribbon under Chart Tools
Click the drop-down arrow on the "Add Chart Element" Icon
Select Grid Lines, then Primary Major Vertical, or Primary Minor Vertical
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Scatter Plot Scales
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Scatter Plot Scales
TTFN
I can do absolutely anything. I'm an expert!
homework forum: //www.engineering.com/AskForum/aff/32.aspx
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers
RE: Scatter Plot Scales
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Scatter Plot Scales
TTFN
I can do absolutely anything. I'm an expert!
homework forum: //www.engineering.com/AskForum/aff/32.aspx
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers
RE: Scatter Plot Scales
And if you needed to plot Date/Time, the Line Chart would not do the job: You'd need a Scatter Chart, But then Months/Years would not be a display factor.
Skip,
for a NUance!