Excel 2007 : # over time with 3 series

[genedjr]

I am trying to create a chart where the x axis is date based and the y axis is a count. Dates are the critical elemement representing a completed action.

In my simple best world, I can define the X axis as a date range, the y axis as a number range, and each series as a set of x,y pairs.

I desire to have each series as a separate line on a single graph.
I have attached my poor attempt.

you help is much appreciated.
...gene

[shg]

You can insert a column to the right of the data as in the example below:

Please Login or Register  to view this content.

The formula in C2 and down is

=IF(B3=B4, NA(), COUNTIF($B$3:$B$19, B3))

Plot that on a column chart.

[genedjr]

Thanks - I did something like this.
The goal is to add up completions over time. So that the various series, in this case, depict differenting geographies, can show cumlative progress over time.

I do this a great deal with planned completions vs actual listed by geo. In general my source data is not in order and the cumlative counts must be calculated. And of course items are often added, deleted, and changed.

I am able to get what I want (thanks again), but it is very manual (see attached). I built the source table by hand. After creating the chart I manually set the x and y axis ranges and told it to connect data points in the line (no need for =NA() at that point).

And I have another one to build that has several hundred entries across the regions.

It seems that my best answer may be in looking at writing some VB (yes, I can code) to setup the data to allow the line chart wizzy to give me this result. With VB it might be easier to create and count the data set, add "planned" and "Actual" series for each region, and format the lines to make it easy to see.

I have not done a chart in VB yet - so I will have to peek over at the programming section to figure out the object model for charts

...gene

[shg]

Connecting the dots with a line doesn't convey any information in this case; in fact, it obscures the irregular nature of the data.

[genedjr]

Ahh - now I see what I failed to communicate.

One series is for installation completion. The total (why they are added together) is how many were complete by that date. Connecting the data with a line shows trending. If we take this apart and put in planned (a series that represents the scheduled completions) and actual (a series that represents when the installation actually occured), you can very quickly see if you are following the plan without being distracted with the actual dates/counts.

Does that make more sence? - here is a must bigger example. (see chart 2 tab)

The attached is the end result I desire (thought I need to experiment with colors and line patterns more).

This is a very small example. Again the real data is only organized by geo, date, and number of installations completed and there are hundreds of installations scheduled.
...gene

[shg]

Right.

But consider this: You have points separated by many days. When you connect them with a line, the line would look the same if there were many other points falling on the same line -- but there aren't. So the line is not only not conveying information, it's obscuring it.

Take a simple example:

1/1 1
2/1 1

That's a straight line at y=1 across a month.

So is this:

1/1 1
1/2 1
1/3 1
...
1/31 1
2/1 1

Those two plots look the same, but mean something entirely different.

[genedjr]

Very good point - so I will add markers.
Thanks
...gene

[shg]

A column chart would be more meaningful and less obfuscatory.

[genedjr]

Interesting - can you take my data and provide an example? I am not sure how a bar chart would convey progress over time against schedule - but I am willing to learn.
...gene

[shg]

Just change the chart type to a column chart. Post an example if you get stuck.