Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

excel:  cumulative data

Posted on 2016-09-13
13
Medium Priority
?
77 Views
Last Modified: 2016-09-13
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
Comment
Question by:Watnog
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 41795776
No File Attached
0
 

Author Comment

by:Watnog
ID: 41795786
It is now...
start_end.xlsx
0
 
LVL 34

Assisted Solution

by:Rob Henson
Rob Henson earned 1000 total points
ID: 41795829
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 1000 total points
ID: 41795831
Hi,

pls try

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

Open in new window

Regards
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 41795841
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
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 41795845
Yes I guessed what the asker wanted
0
 

Author Comment

by:Watnog
ID: 41795856
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
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 41795862
by entering in the formula it actualized
start_end_Av1.xlsx
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 41795871
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
 

Author Comment

by:Watnog
ID: 41795898
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
 
LVL 34

Expert Comment

by:Rob Henson
ID: 41795917
Do you have a circular reference somewhere in the formulas?

Bottom left of the window should confirm.
0
 

Author Comment

by:Watnog
ID: 41796408
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
 

Author Closing Comment

by:Watnog
ID: 41796412
Thanks guys.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question