Creating a "complicated" run-rate chart from MS Project Data

[Pete Mccann]

Each month we need to prepare project charts created from data in MS Project Plans of Work (POWs). We have a method of doing this but it involves lots of excel formulae and chart manipulation. The MS Project POWs track Installation activities. All the POW tasks run consecutively and there are no overlaps (i.e. one task finishes before the next task can start). The attached Word document summarizes the situation and the question; the Excel worksheet shows how we do it today.
We are looking for an easier / simpler way to create the chart (maybe combine formulae, Pivot Tables etc?). One complication is that the timeline for the two POWs (X and (X-1)) are different.

 

[p45cal]

In the attached (it's only a start), there's only 1 sheet. It's your Charts sheet but your chart has been copied in situ as a picture, just for comparison.
There's also a proper Excel chart on that sheet with 4 lines whose data sources derive only from the data on that sheet, ie. the two little tables at the top and the manual copying of Actual data (transposed) against the right dates.
My concern is that your chart was made up of some 14 or so series, many of which overlapped - I wondered if there was a specific reason to do that.

I've changed my mind, there are 2 sheets, I've copied that sheet (to Chart (2)) and removed everything except what pertains to Pow-X planned, just to show what's involved in plotting just 1 series on the chart. Other series are created in the same way.

Is my suggestion at least significantly simpler than the existing solution?

I'm still pondering on your Pivot table question…

edit post posting:
I've just re-read you Word document, and 2 points:
1. You can see how two timelines are successfully combined.
2. I think the individual run rates can be highlighted within the single line by colourng different portions of that line. A little macro could do that.

 

[Pete Mccann]

Hi - many thanks for this. I had not considered the VLOOKUP to do this. Yes, there is a specific reason for having 14 or so series on the chart. Plotting the series overlapping (and then giving each series a different colour) allows each element (task) in the chart to be seen. Even though there are, say, 14 tasks in the plan, there are only 4 distinct types of tasks (that is why there are only 4 different colours in the chart). I think your approach might allow me to do a set of calculations for Task 1, Task 2 ....Task 14 and then to plot them all on top of each other. By rearranging the order of the way the series are plotted and then changing the colours of each series, I might end up with a similar chart. However, I think I still need to have the NA calculation in there somewhere to "stop" a particular series at a logical point (otherwise (in my method) the series curve drops to zero and spoils to look of the chart).

 

[p45cal]

Just for demo purposes, in the attached in the Chart (2) sheet, a chart with a single series, with parts of the line in different colours.
There's a button which runs a little macro that colours the line. Play about with the group name associated with each task in cells W7:W19, and play about with the background colour of the cells Y1:Y4. You should also be able to add to/change the group names and background colours in column Y, as long as you use the same group names (or a subset of them) in W7:W19. If you leave them blank (or the name isn't found in column Y) then the line won't change colour for that section; manually change the whole line to a base colour before clicking the button if that's what you want to happen (I've added another button to do this).

Yes, the NA result can be included to stop a line showing. For example, by changing the formula in V23 to:
=IF(OR($S23>MAX($S$7:$T$19),$S23<MIN($S$7:$T$19)),NA(),U23/U$166)
and copying down.

 

[Pete Mccann]

Dear p45cal, many thanks again for your help with this task. Again, I had not considered a macro to select distinct parts of the data set to allow specific colours to be applied to the chart. This is a very nice solution.