Solved

excel:  cumulative data

Posted on 2016-09-13
13
69 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 33

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 52

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 33

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 52

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 52

Expert Comment

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

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 33

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

628 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