Solved

excel:  cumulative data

Posted on 2016-09-13
13
47 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:itjockey
ID: 41795776
No File Attached
0
 

Author Comment

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

Assisted Solution

by:Rob Henson
Rob Henson earned 250 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
 
LVL 48

Accepted Solution

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

pls try

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

Open in new window

Regards
0
 
LVL 31

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 48

Expert Comment

by:Rgonzo1971
ID: 41795845
Yes I guessed what the asker wanted
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 48

Expert Comment

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

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 31

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now