• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 85
  • Last Modified:

excel: cumulative data

Hi Experts,

I have a long list of jobs that show start time and end time which gives runtime.
It's easy enough to know how many started (count subtotal on change of starttime) but i need I need to know how many jobs were active (so started + already running) at a certain time however.

Can it be done with these data?

START_TIME                       END_TIME                                                     RUNTIME
12/09/2016 0:02:18      12/09/2016 0:02:26                              0:00:08
12/09/2016 0:02:18      12/09/2016 0:03:12                              0:00:54
12/09/2016 0:02:18      12/09/2016 0:05:05                              0:02:47
12/09/2016 0:02:18      12/09/2016 0:03:11                              0:00:53
12/09/2016 0:02:19      12/09/2016 0:03:30                              0:01:11
12/09/2016 0:02:19      12/09/2016 0:03:29                              0:01:10
12/09/2016 0:02:19      12/09/2016 0:03:26                              0:01:07
12/09/2016 0:02:19      12/09/2016 0:03:28                              0:01:09
12/09/2016 0:02:19      12/09/2016 0:03:28                              0:01:09
12/09/2016 0:02:19      12/09/2016 0:03:28                              0:01:09
12/09/2016 0:02:19      12/09/2016 0:03:05                              0:00:46
12/09/2016 0:02:19      12/09/2016 0:09:23                              0:07:04
12/09/2016 0:02:19      12/09/2016 0:03:27                              0:01:08
12/09/2016 0:02:19      12/09/2016 0:03:30                              0:01:11
12/09/2016 0:02:19      12/09/2016 0:03:26                              0:01:07
12/09/2016 0:02:20      12/09/2016 0:03:07                              0:00:47
...



At 00:02:18 4 jobs kicked in, those were the first so total is '4'
At 00:02:19 11 jobs kicked in, the 4 that started 00:02:18 were still running so that makes '15'
...
At 00:02:27 more jobs kicked in so those would count but the first one in this list has ended by that time so doesn't count
...
etcetera

I hope i make myself clear.
Not sure if this is possible, but it would be great if it could.
Thanks for your help

I attach a file with 'full' data.

Watnog.
0
Watnog
Asked:
Watnog
  • 5
  • 4
  • 3
  • +1
2 Solutions
 
Naresh PatelTraderCommented:
No File Attached
0
 
WatnogAuthor Commented:
It is now...
start_end.xlsx
0
 
Rob HensonFinance AnalystCommented:
First sort the data by Start Time and then Finish Time and then I think this set of calculations will do what you want:

Column E: count Started  =COUNTA(A2:$A$2)
Column F: count Finished by Start time for row:  =COUNTIF(B$2:B2,"<"&A2)
Column G: Started - Finished  =E2-F2

Or as one formula:

=COUNTA(A2:$A$2)-COUNTIF(B$2:B2,"<"&A2)

Copy down the columns.

Thanks
Rob H
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Rgonzo1971Commented:
Hi,

pls try

=COUNTIFS(A:A,"<="&--A2,A:A,"<="&--B2,B:B,">="&--A2)

Open in new window

Regards
0
 
Rob HensonFinance AnalystCommented:
Hi Rgonzo,

Our figures agree except mine shows the incremental increase/decrease for each job starting.

For example, your first 4 jobs all show 4, mine increment from 1 to 4; yours then jumps to 42 where mines increments to 42; yours then jumps to 48 etc etc

Thanks
Rob H
0
 
Rgonzo1971Commented:
Yes I guessed what the asker wanted
0
 
WatnogAuthor Commented:
Just doesn't seem to work out on my system...
Can you check that out in the new file attached.

Thanks for your help.
start_end_A.xlsx
0
 
Rgonzo1971Commented:
by entering in the formula it actualized
start_end_Av1.xlsx
0
 
Rob HensonFinance AnalystCommented:
With the file you uploaded, the formulas were correct. I went into the first of each and pressed F2 and enter to force calculation and then copied down the columns.

I think that is what Rgonzo is saying as well.

Thanks
Rob H
0
 
WatnogAuthor Commented:
That's weird, i can see  your values/formula as long as in proctected mode, as soon as I go into edit all values turn to zero.
I googled that but it's not clear yet what's causing this.
0
 
Rob HensonFinance AnalystCommented:
Do you have a circular reference somewhere in the formulas?

Bottom left of the window should confirm.
0
 
WatnogAuthor Commented:
I ended up working things out on my  private laptop:  couldn't figure out why the COUNTIF returned "0" on this one. Having over 120K rows doesn't help of course, but looks good now.
Thanks both very much for your help in this.
Cheers.
0
 
WatnogAuthor Commented:
Thanks guys.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now