Graph not showing data with equal steps

[willem1102]

I need some help with showing the right data in a graph.

I need to show for multiple projects that the man-hours that are spent during the project are not equally devided over the time course of the project. (the first 70% of hours is often spent during the first 50% of the time).

I have data for 20 projects on what hours are spent on which days.
I started with one project. for this project I set the start end end date of the projects and made running totals of both the time and hours. when I make a graph of these two against eachother it shows really strange steps in the axis of the time:
Graph with wrong steps.PNG

This is because there are no records of hours in for example the last 26%, how can I make this graph so that the steps are equal to make me able to compare the different project with eachother? Or is there maybe another way to do this for all projects without having to go to all the trouble I just explained?

I got a tip saying that I had to interpolate the data so that I get data corresponding to the time with steps like 5% 10% 15% etc. I tried that but I do not know how to do this effeciently so that it does not take a couple of hours for every project, since I have to do 20. Right now the points I have in both the time and hours are for example:

TIME - HOURS
0,8170% - 3,69%
0,9804% - 4,51%
1,1438% - 5,32%

and so on....

Could someone help me?

Thank you

(is this the right section to place the thread or should I place it somewhere else?)

[MrShorty]

It can be difficult to debug this sort of thing from a picture. If I read your picture correctly, it looks like you are using a line chart with a category/text axis. Your description sounds to me like you want an XY scatter chart instead. Can you provide a better explanation of what you are wanting your chart to show? Is there some reason you are using preferring a line chart over a scatter chart for this data?

[willem1102]

Thank you for the reaction. I did uctually use the wrong chart, thank you. But the question that still remains is how can I compare the 20 projects and make 1 mean hours vs time progress curve when all the data is different. See the attachment for a example with 2 projects.

[Richard Buttrey]

It's simple enough to add a second and subsequent series to the first chart and see two, three, ..etc. plots, but which values are you comparing to strike a Mean series so that you can plot that. As you say the projects are not for the same time periods, nor even the same time but in a different year.

One way would be to sum each of the 10th percentiles for each project, average those and plot that as the average/mean series.

See attached where I've added the 2nd series to the 1st chart.

[willem1102]

Thank you for the reaction, I'm going to try that. Some projects might not have data that corresponds to all the 10th percentiles. As you can see at the second series in the attachment (and also in the picture), the data takes a step from 74% to 100% in the time axis.

Is there a way to make the 10th percentiles quickly or do I have to do it manually per percentile for each project?

[Richard Buttrey]

Thank you for the reaction, I'm going to try that. Some projects might not have data that corresponds to all the 10th percentiles. As you can see at the second series in the attachment (and also in the picture), the data takes a step from 74% to 100% in the time axis.

Is there a way to make the 10th percentiles quickly or do I have to do it manually per percentile for each project?

By definition all series will have a 10th and all subsequent percentiles!

I've added the 10th percentiles and a new series for the mean of the two series.
If you want greater granularity then in extremis you could plot every single percentile and extend the percentile table to 100 rows. But since you're averaging out data I suggest that would be spurious accuracy and the 10th percentiles or even the 5th percentiles would be sufficient

[MrShorty]

To put all of the data series onto one chart:

1) Create the chart using one of the data series (like you have already done).
2) Pull up the "Select Data" dialog for that chart and use the "Add" button to add the other data series to the chart.

I am not sure what you mean by "make 1 mean hours vs time progress curve". Are you looking to add some kind of "average"/"arithmetic mean" series to the chart? If so, I would expect to accomplish this by:

3) Compute the desired mean/average curve in the spreadsheet. You have not explained exactly how you would like to compute this average, so I cannot make any specific recommendations.
4) Add the average/mean to the chart as another data series using the Select Data dialog.