Excel Scatter Chart - X-axis with Months?

[itchyII]

Hi Everyone,
I have a little bit of a problem. I am using Excel 2000 and I need to create a chart. I have data that consists of Projects, Dates, and averages. I need to plot these on a chart, in a realistic manner and have the chart plot a trend line. I have chosen to do this using a scatter chart because the line chart does not plot the dates realistically (spaced according to the real date). Here is an example of my data and what I am looking for:

Project Date Average
ProjectA 1/15/04 3
ProjectB 2/20/04 4
ProjectC 3/18/04 4
ProjectD 6/25/04 5


5| ProjectC x
4| x x ProjectD
3| x ProjectB
2| ProjectA
1|_____________________________________________________
Jan04 Feb04 Mar04 Apr04 May04 Jun04 Jul04

**The trend line is not shown in my example, but it should be there

I have encountered two problems achieving this graph.

Problem 1) When I use a scatter chart, I am unable to have the x-axis by month. I can set it for a specific number of days, say 30 or 31, but when I do that, obviously, the axis is not really monthly and eventually I end up with the same month appearing twice.

Problem 2) I am unable to get the label on the data points to be only the Project (not the date, or the date and the project.

As for #1, the best I have come up with is to create a custom combination chart of a scatter and a line graph, where the line graph has the primary x-axis, and I define is as a Time-scale but I make all the data points and lines invisible so they do not show up on the chart. I then defined the scatter x-axis to be every 30 days and plot the data on the scatter and the ‘hide’ the secondary x-axis by using a white font. This work around seems to look ok, but there has to be an easier way to do this! I am scheduled to leave my job in 3 weeks and someone is going to have to take over for me, they are not going to understand this!

As for #2, I say some interesting code by Skip in this thread thread68-872546, I wonder if it will work. And if so, how will I isolate the data series of the scatter chart and not the line chart (given that I continue with my solution for problem #1)

Your input is greatly appreciated!

ItchyII

 

[GlennUK]

Hi ItchyII,

whenever I plot against dates I don't have single months for the x-axis, as months are different lengths and the scaling wouldn't really make sense. I have the scaling ticked at 28 day intervals, and format the scaling number to show dd-mmm as the x-axis format, which shows 13 slots for a year. This makes for a really easy x-y scatter for date.

As for the labelling of the points look at this link, for Rob Bovey's x-y scatter chart labeller:

http://www.appspro.com/Utilities/ChartLabeler.htm

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[itchyII]

Hi Glenn
Thanks for you response. I appreciate your suggestion about the 28 day scale, but my employer is not as understanding. He doesn't accept that it's 'not possible'. He believes that everything is possible! As for your suggestion about the labeller, it looks great, however, I work in a Military environment and we cannot download or install anything on our pc's that is not approved by the IT security group. I need to find a way to code this myself.

Thanks anyways!

ItchyII

 

[GlennUK]

Hi again ItchyII,

why don't you input a series of dates into your spreadsheet in a range of cells going vertical, for the first of each month of the year, with a zero data item in the next cell to the right of each date, and copy the series to the chart, and have the series labels showing instead of using the scale. I've just had a play with this, and with a bit of fiddling it looks ok.

As for writing an x-y labeller, activate the chart, and run this macro:

Sub formlab()
    mystart = 1
    mycount = 0
     For Each p In ActiveChart.SeriesCollection(1).Points
        mycount = mycount + 1
        p.HasDataLabel = True
        p.DataLabel.Text = Workbooks("Itchybook.xls").Sheets("Datasheet").Range("A" & mystart + mycount).Value
    Next
End Sub

changing Itchybook.xls to your bookname, and Datasheet to the required sheetname. Assuming the labels are in A2 downwards.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.