Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

excel:  cumulative data

Posted on 2016-09-13
13
Medium Priority
?
74 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 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 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 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 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

664 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