Cumulative graph within a number of date ranges?

Hello all,

I am trying to put together a cumulative graph within a date range and am not sure how to do this?

This is what I am trying to put into a graph form;

I have a number of date ranges and within each date range is a number I would like to show in the y axis.
e.g. i require 50 trains every day between dates 01/01/2016 - 01/01/2017 for one activity
i also require 30 trains every day between 01/05/2016 - 01/07/2017 for another activity

At some point they overlap at which points i would like to plot the sum of them. So the y axis would show the number of trains at any given time and the x axis would show the date.
i.e. for the example above between 01/01/2016 - 01/05/2016 the graph would show 50 of the y axis, between 01/05/2016 - 01/01/2017 it would show 80 (50+30), and between 01/01/2017 - 01/07/2017 it would show 30.

I have several date ranges so this would need to work with an infinite amount of data.

Thanks in advance. It is difficult to explain so please let me know if you need any more info.

Hi,

Welcome to the Forum.

Can you post a sample template of your workbook please?

http://www.excelforum.com/members/da...ch-a-file.html

Hi Thanks for getting back to me.

This is a template of the information (attached) i would like to capture in graph format.

Thanks.

Is it the details on Sheet1 that you want to capture in a graph?

As your data is for trains/week, would you like to graph things on a weekly basis, or monthly, considering the date ranges that you want to cover?

Pete

Yes the details are summarized on sheet 1.

The graph would probably be easiest to read on a monthly if not quarterly basis. I think monthly would be sensible.

Thanks.

See the attached file.

I've inserted a new row 1 and set up monthly dates from column E across in row 2. The formula in E3 is:

=IF($B3>=E$2,IF($B3<MIN($C3+1,DATE(YEAR(E$2),MONTH(E$2)+1,1)),MIN($C3+1,DATE(YEAR(E$2),MONTH(E$2)+1,1))-$B3,0),IF($C3<E$2,0,IF($C3>=DATE(YEAR(E$2),MONTH(E$2)+1,1),DATE(YEAR(E$2),MONTH(E$2)+1,1)-E$2,$C3-E$2+1)))*$A3/7

Most of this works out the number of days that occur in the month, and this is then multiplied by the number of trains per day. The formula can be copied across and down as required to give the number of trains per month for each time period. I've use row 1 to get the totals for each month, and this is then graphed - you can add your own embellishments like titles etc.

Hope this helps.

Pete

Thanks Pete.

That's perfect. I presume if I ever wanted to change it to weeks I would change month for week in the formula?

Thanks again.

Originally Posted by JS17

... I presume if I ever wanted to change it to weeks I would change month for week in the formula?

It's a bit more involved than that, although the formulae are slightly simpler. You should put the starting date of the first week you are interested in into cell E2 (e.g. the first Monday) and put this formula in F2:

=E2+7

Copy this across as far as you need to. Then you can put this formula in E3:

=IF($B3>=E$2,IF($B3<MIN($C3+1,E$2+7),MIN($C3+1,E$2+7)-$B3,0),IF($C3<E$2,0,IF($C3>=E$2+7,E$2+7-E$2,$C3-E$2+1)))*$A3/7

and copy this across and down as required.

I've done this in the attached file, and also changed the graph type to LINE.

Hope this helps.

Pete

P.S. If that has solved your problem, please mark the thread as SOLVED by clicking on Thread Tools above your first post

Thanks Pete