Hi, I have the same requirement and I was running your macro. It is giving
me a syntax error. I am using excel 2007.
The error is at
Summary = Summary & "There were " & Frequency(X) & " times " & X & _
Next
What should I do ?
Rick Rothstein \(MVP - VB\) wrote:
Okay, here is my take on it.
29-Mar-08
Okay, here is my take on it. Assuming your start date/time is in Column A,
your duration is in Column B, and your ending date/time is in Column C,
run
the following macro from the code page that your data is located on. When
the code is finished running (I have no idea how long it will take to
process 250,000 rows of data), a message box will appear showing the usage
frequency on a per second basis.
Rick
Sub GetMaxUsageBySecond()
Dim X As Long
Dim Z As Long
Dim Offset As Long
Dim LastRow As Long
Dim TotalSeconds As Long
Dim MaxStartTimeSeconds As Long
Dim Duration As Long
Dim MaxSeconds As Long
Dim Seconds() As Long
Dim Frequency() As Long
Dim FirstDateTime As Double
Dim LastDateTime As Double
Dim LastStartTime As Double
Dim MaxTime As Date
Dim MaximumDate As Date
Dim Summary As String
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
FirstDateTime = Range("A1").Value
LastDateTime = Cells(LastRow, "C").Value
LastStartTime = Cells(LastRow, "A").Value
MaxStartTimeSeconds = DateDiff("s", FirstDateTime, LastStartTime)
MaximumDate = Application.Evaluate("=MAX(C1:C" & _
Cells(Rows.Count, "C").End(xlUp).Row & ")")
TotalSeconds = DateDiff("s", FirstDateTime, MaximumDate)
ReDim Seconds(0 To TotalSeconds)
For X = 1 To LastRow
Duration = Cells(X, "B").Value
Offset = DateDiff("s", FirstDateTime, Cells(X, "A").Value)
For Z = Offset To Offset + Duration
Seconds(Z) = Seconds(Z) + 1
Next
Next
For X = 0 To TotalSeconds
If Seconds(X) > MaxSeconds Then
MaxSeconds = Seconds(X)
MaxTime = FirstDateTime + TimeSerial(0, 0, X)
End If
Next
ReDim Frequency(0 To MaxSeconds)
For X = 0 To TotalSeconds
Frequency(Seconds(X)) = Frequency(Seconds(X)) + 1
Next
For X = MaxSeconds To 0 Step -1
Summary = Summary & "There were " & Frequency(X) & " times " & X & _
" lines were in use at the same time." & vbNewLine
Next
MsgBox Summary
End Sub
Previous Posts In This Thread:
Calculate actual trunk utilization based on call start/duration
I am 'attempting' to calculate our phone line utilization at work. The
end goal is to determine how many lines we actually use at any given
point. I thought this would be an easy problem, however the more I
work on solving it, the more difficult it becomes.
I have roughly 250,000 calls (I'm obviously using excel 2007) that I
am attempting to run calculations against. Here is a sampling of the
data.
Start Duration End
3/19/07 6:39:59 AM 143 3/19/07 6:42:22 AM
3/19/07 6:45:7 AM 9 3/19/07 6:45:16 AM
3/19/07 6:54:25 AM 14 3/19/07 6:54:39 AM
3/19/07 7:03:25 AM 50 3/19/07 7:04:15 AM
3/19/07 7:04:4 AM 18 3/19/07 7:04:22 AM
3/19/07 7:07:6 AM 25 3/19/07 7:07:31 AM
3/19/07 7:10:41 AM 267 3/19/07 7:15:8 AM
3/19/07 7:12:30 AM 29 3/19/07 7:12:59 AM
3/19/07 7:12:41 AM 18 3/19/07 7:12:59 AM
3/19/07 7:37:45 AM 37 3/19/07 7:38:22 AM
3/19/07 7:42:50 AM 9 3/19/07 7:42:59 AM
3/19/07 7:43:37 AM 7 3/19/07 7:43:44 AM
3/19/07 7:44:58 AM 43 3/19/07 7:45:41 AM
3/19/07 7:53:46 AM 75 3/19/07 7:55:1 AM
3/19/07 7:56:30 AM 34 3/19/07 7:57:4 AM
3/19/07 7:59:38 AM 312 3/19/07 8:04:50 AM
3/19/07 8:00:2 AM 9 3/19/07 8:00:11 AM
3/19/07 8:00:7 AM 28 3/19/07 8:00:35 AM
3/19/07 8:01:7 AM 32 3/19/07 8:01:39 AM
3/19/07 8:01:13 AM 45 3/19/07 8:01:58 AM
3/19/07 8:02:36 AM 23 3/19/07 8:02:59 AM
3/19/07 8:05:53 AM 2 3/19/07 8:05:55 AM
3/19/07 8:10:33 AM 21 3/19/07 8:10:54 AM
3/19/07 8:12:14 AM 21 3/19/07 8:12:35 AM
3/19/07 8:13:5 AM 43 3/19/07 8:13:48 AM
3/19/07 8:14:13 AM 32 3/19/07 8:14:45 AM
3/19/07 8:14:17 AM 33 3/19/07 8:14:50 AM
3/19/07 8:14:21 AM 6 3/19/07 8:14:27 AM
3/19/07 8:14:21 AM 33 3/19/07 8:14:54 AM
3/19/07 8:14:29 AM 32 3/19/07 8:15:1 AM
3/19/07 8:14:39 AM 41 3/19/07 8:15:20 AM
3/19/07 8:15:30 AM 81 3/19/07 8:16:51 AM
3/19/07 8:18:21 AM 587 3/19/07 8:28:8 AM
3/19/07 8:18:55 AM 56 3/19/07 8:19:51 AM
3/19/07 8:20:44 AM 148 3/19/07 8:23:12 AM
3/19/07 8:20:52 AM 1002 3/19/07 8:37:34 AM
3/19/07 8:21:42 AM 23 3/19/07 8:22:5 AM
3/19/07 8:22:43 AM 33 3/19/07 8:23:16 AM
3/19/07 8:22:59 AM 855 3/19/07 8:37:14 AM
3/19/07 8:23:50 AM 1087 3/19/07 8:41:57 AM
3/19/07 8:25:58 AM 22 3/19/07 8:26:20 AM
3/19/07 8:27:57 AM 42 3/19/07 8:28:39 AM
3/19/07 8:28:53 AM 17 3/19/07 8:29:10 AM
3/19/07 8:29:8 AM 10 3/19/07 8:29:18 AM
3/19/07 8:30:1 AM 58 3/19/07 8:30:59 AM
3/19/07 8:30:27 AM 196 3/19/07 8:33:43 AM
3/19/07 8:31:49 AM 2 3/19/07 8:31:51 AM
3/19/07 8:31:56 AM 221 3/19/07 8:35:37 AM
3/19/07 8:32:16 AM 535 3/19/07 8:41:11 AM
3/19/07 8:32:26 AM 178 3/19/07 8:35:24 AM
3/19/07 8:32:35 AM 18 3/19/07 8:32:53 AM
3/19/07 8:32:47 AM 35 3/19/07 8:33:22 AM
3/19/07 8:33:5 AM 28 3/19/07 8:33:33 AM
3/19/07 8:33:11 AM 1 3/19/07 8:33:12 AM
3/19/07 8:33:19 AM 68 3/19/07 8:34:27 AM
3/19/07 8:34:11 AM 143 3/19/07 8:36:34 AM
3/19/07 8:34:17 AM 177 3/19/07 8:37:14 AM
3/19/07 8:36:20 AM 17 3/19/07 8:36:37 AM
3/19/07 8:36:26 AM 57 3/19/07 8:37:23 AM
3/19/07 8:36:39 AM 333 3/19/07 8:42:12 AM
3/19/07 8:38:22 AM 44 3/19/07 8:39:6 AM
3/19/07 8:39:0 AM 2 3/19/07 8:39:2 AM
3/19/07 8:40:4 AM 85 3/19/07 8:41:29 AM
3/19/07 8:40:28 AM 2 3/19/07 8:40:30 AM
3/19/07 8:41:3 AM 30 3/19/07 8:41:33 AM
3/19/07 8:42:14 AM 511 3/19/07 8:50:45 AM
3/19/07 8:42:22 AM 882 3/19/07 8:57:4 AM
3/19/07 8:42:24 AM 94 3/19/07 8:43:58 AM
This 'simple' problem is probably the most difficult issue I have
encountered. The issue is a short phone call ties up the line but then
releases it again - so getting totals has proved difficult.
In the end, I would like to be able to graph the results to visually
be able to tell how many times we have hit certain numbers.
I am hoping to merge several sites together via VOIP and need a way to
determine how many lines we actually need.
Good Luck and Thanks,
Nathaniel
RE: Calculate actual trunk utilization based on call start/duration
Let's say the Start data is in column A1:A63 and the END data is in Column
C1:C68.
Put this in D1 and copy down.
=SUMPRODUCT(--($A$1:$A$68>=A1),--($C$1:$C$68<=C1))
--
HTH,
Barb Reinhardt
Now that I think of it, this may not be exactly what you want.
Now that I think of it, this may not be exactly what you want. But it's
close.
You could set up another table with START/END time increments of 1 minute
or
10 minutes, whatever you want and use a similar SUMPRODUCT calculation.
If
you need help, let me know.
--
HTH,
Barb Reinhardt
:
I haven't seen this problem in almost 30 years since I was going for my
I haven't seen this problem in almost 30 years since I was going for my
masters in computer science. I went to Steven's Institute of Technology
where many of the courses were taught by engineers from Bell Labs.
I'm using two worksheets. Sheet1 contains your input data. Not usre
which
data is in which columns. I have the following
A: Date
B: Start Time
C: Phone Line
D: Date
E: End time
In sheet two the macro creates a row for each minute (0 to 1439) where
there
are 1440 minutes in a day. The code then looks at each phone call and
adds 1
to column B of sheet two for each minute the phone call was active. The
code
then goes to each phone call and repeates the process.
The code is only set up for one day. You probably want to modifiy the
code
that a diferent column in sheet 2 is used for each day. If you need
additional help let me know.
Sub getusage()
RowCount = 1
For MyMinute = 0 To 1439
With Sheets("Sheet2")
.Range("A" & RowCount) = _
TimeSerial(Int(MyMinute / 60), MyMinute Mod 60, 0)
RowCount = RowCount + 1
End With
Next MyMinute
RowCount = 1
With Sheets("Sheet1")
Do While .Range("A" & RowCount) <> ""
StartTime = .Range("B" & RowCount)
EndTime = .Range("E" & RowCount)
MyTime = TimeSerial(Hour(StartTime), Minute(StartTime), 0)
With Sheets("Sheet2")
Set c = .Columns("A:A").Find(what:=MyTime, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
RowOffset = 0
Do While c.Offset(RowOffset, 0) <= EndTime
c.Offset(RowOffset, 1) = _
c.Offset(RowOffset, 1) + 1
RowOffset = RowOffset + 1
Loop
End If
End With
RowCount = RowCount + 1
Loop
End With
End Sub
:
This is a GREAT problem!
This is a GREAT problem! It falls in the area of Resource Utilization and
many thick books have been written on the topic. You can use Excel with
some
simple formulas to get information about your data. Let???s say we split
the
day up into 1 minute intervals and we want to know how many calls fell
into
each interval.
I took your data and put the start date/time in column A, the duration in
column B, and the end date/time in column C. I began in row #3. In D1
and
D2 we enter:
3/19/2007 8:00:00
3/19/2007 8:01:00
the beginning and end of a typical one minute window. In D3 we enter:
=($D$2>A3)*($D$1<C3) and copy down. We see in A1 thru D70:
3/19/2007 8:00:00
3/19/2007 8:01:00
3/19/2007 6:39:59 143 3/19/2007 6:42:22 0
3/19/2007 6:45:07 9 3/19/2007 6:45:16 0
3/19/2007 6:54:25 14 3/19/2007 6:54:39 0
3/19/2007 7:03:25 50 3/19/2007 7:04:15 0
3/19/2007 7:04:04 18 3/19/2007 7:04:22 0
3/19/2007 7:07:06 25 3/19/2007 7:07:31 0
3/19/2007 7:10:41 267 3/19/2007 7:15:08 0
3/19/2007 7:12:30 29 3/19/2007 7:12:59 0
3/19/2007 7:12:41 18 3/19/2007 7:12:59 0
3/19/2007 7:37:45 37 3/19/2007 7:38:22 0
3/19/2007 7:42:50 9 3/19/2007 7:42:59 0
3/19/2007 7:43:37 7 3/19/2007 7:43:44 0
3/19/2007 7:44:58 43 3/19/2007 7:45:41 0
3/19/2007 7:53:46 75 3/19/2007 7:55:01 0
3/19/2007 7:56:30 34 3/19/2007 7:57:04 0
3/19/2007 7:59:38 312 3/19/2007 8:04:50 1
3/19/2007 8:00:02 9 3/19/2007 8:00:11 1
3/19/2007 8:00:07 28 3/19/2007 8:00:35 1
3/19/2007 8:01:07 32 3/19/2007 8:01:39 0
3/19/2007 8:01:13 45 3/19/2007 8:01:58 0
3/19/2007 8:02:36 23 3/19/2007 8:02:59 0
3/19/2007 8:05:53 2 3/19/2007 8:05:55 0
3/19/2007 8:10:33 21 3/19/2007 8:10:54 0
3/19/2007 8:12:14 21 3/19/2007 8:12:35 0
3/19/2007 8:13:05 43 3/19/2007 8:13:48 0
3/19/2007 8:14:13 32 3/19/2007 8:14:45 0
3/19/2007 8:14:17 33 3/19/2007 8:14:50 0
3/19/2007 8:14:21 6 3/19/2007 8:14:27 0
3/19/2007 8:14:21 33 3/19/2007 8:14:54 0
3/19/2007 8:14:29 32 3/19/2007 8:15:01 0
3/19/2007 8:14:39 41 3/19/2007 8:15:20 0
3/19/2007 8:15:30 81 3/19/2007 8:16:51 0
3/19/2007 8:18:21 587 3/19/2007 8:28:08 0
3/19/2007 8:18:55 56 3/19/2007 8:19:51 0
3/19/2007 8:20:44 148 3/19/2007 8:23:12 0
3/19/2007 8:20:52 1002 3/19/2007 8:37:34 0
3/19/2007 8:21:42 23 3/19/2007 8:22:05 0
3/19/2007 8:22:43 33 3/19/2007 8:23:16 0
3/19/2007 8:22:59 855 3/19/2007 8:37:14 0
3/19/2007 8:23:50 1087 3/19/2007 8:41:57 0
3/19/2007 8:25:58 22 3/19/2007 8:26:20 0
3/19/2007 8:27:57 42 3/19/2007 8:28:39 0
3/19/2007 8:28:53 17 3/19/2007 8:29:10 0
3/19/2007 8:29:08 10 3/19/2007 8:29:18 0
3/19/2007 8:30:01 58 3/19/2007 8:30:59 0
3/19/2007 8:30:27 196 3/19/2007 8:33:43 0
3/19/2007 8:31:49 2 3/19/2007 8:31:51 0
3/19/2007 8:31:56 221 3/19/2007 8:35:37 0
3/19/2007 8:32:16 535 3/19/2007 8:41:11 0
3/19/2007 8:32:26 178 3/19/2007 8:35:24 0
3/19/2007 8:32:35 18 3/19/2007 8:32:53 0
3/19/2007 8:32:47 35 3/19/2007 8:33:22 0
3/19/2007 8:33:05 28 3/19/2007 8:33:33 0
3/19/2007 8:33:11 1 3/19/2007 8:33:12 0
3/19/2007 8:33:19 68 3/19/2007 8:34:27 0
3/19/2007 8:34:11 143 3/19/2007 8:36:34 0
3/19/2007 8:34:17 177 3/19/2007 8:37:14 0
3/19/2007 8:36:20 17 3/19/2007 8:36:37 0
3/19/2007 8:36:26 57 3/19/2007 8:37:23 0
3/19/2007 8:36:39 333 3/19/2007 8:42:12 0
3/19/2007 8:38:22 44 3/19/2007 8:39:06 0
3/19/2007 8:39:00 2 3/19/2007 8:39:02 0
3/19/2007 8:40:04 85 3/19/2007 8:41:29 0
3/19/2007 8:40:28 2 3/19/2007 8:40:30 0
3/19/2007 8:41:03 30 3/19/2007 8:41:33 0
3/19/2007 8:42:14 511 3/19/2007 8:50:45 0
3/19/2007 8:42:22 882 3/19/2007 8:57:04 0
3/19/2007 8:42:24 94 3/19/2007 8:43:58 0
This means that three calls fell into the window. Now we just continue
with
column E for the next interval, etc. In the end we can create a
minute-by-minute histogram of phone usage.
But this is only the beginning. If the three calls were tiny, they may
not
overlap at all, or they may overlap completely! I suggest you begin by
checking out:
http://www.google.com/search?hl=en&q=algorithm+resource+utilization&btnG=Google+Search
to see if a solution has already been published.
--
Gary''s Student - gsnu200776
:
Okay, here is my take on it.
Okay, here is my take on it. Assuming your start date/time is in Column A,
your duration is in Column B, and your ending date/time is in Column C,
run
the following macro from the code page that your data is located on. When
the code is finished running (I have no idea how long it will take to
process 250,000 rows of data), a message box will appear showing the usage
frequency on a per second basis.
Rick
Sub GetMaxUsageBySecond()
Dim X As Long
Dim Z As Long
Dim Offset As Long
Dim LastRow As Long
Dim TotalSeconds As Long
Dim MaxStartTimeSeconds As Long
Dim Duration As Long
Dim MaxSeconds As Long
Dim Seconds() As Long
Dim Frequency() As Long
Dim FirstDateTime As Double
Dim LastDateTime As Double
Dim LastStartTime As Double
Dim MaxTime As Date
Dim MaximumDate As Date
Dim Summary As String
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
FirstDateTime = Range("A1").Value
LastDateTime = Cells(LastRow, "C").Value
LastStartTime = Cells(LastRow, "A").Value
MaxStartTimeSeconds = DateDiff("s", FirstDateTime, LastStartTime)
MaximumDate = Application.Evaluate("=MAX(C1:C" & _
Cells(Rows.Count, "C").End(xlUp).Row & ")")
TotalSeconds = DateDiff("s", FirstDateTime, MaximumDate)
ReDim Seconds(0 To TotalSeconds)
For X = 1 To LastRow
Duration = Cells(X, "B").Value
Offset = DateDiff("s", FirstDateTime, Cells(X, "A").Value)
For Z = Offset To Offset + Duration
Seconds(Z) = Seconds(Z) + 1
Next
Next
For X = 0 To TotalSeconds
If Seconds(X) > MaxSeconds Then
MaxSeconds = Seconds(X)
MaxTime = FirstDateTime + TimeSerial(0, 0, X)
End If
Next
ReDim Frequency(0 To MaxSeconds)
For X = 0 To TotalSeconds
Frequency(Seconds(X)) = Frequency(Seconds(X)) + 1
Next
For X = MaxSeconds To 0 Step -1
Summary = Summary & "There were " & Frequency(X) & " times " & X & _
" lines were in use at the same time." & vbNewLine
Next
MsgBox Summary
End Sub
By the way, I forgot to mention, if you want to plot the number of
concurrent
By the way, I forgot to mention, if you want to plot the number of
concurrent lines in use per second, use the Seconds array. The zeroeth
array
element contains the number of lines in use for the first "date/time" in
your list, array element one contains the number of lines in use one
second
later, and so on.
Rick
message
If you come back to this thread before your test run, you can eliminate
the
If you come back to this thread before your test run, you can eliminate
the
MaxTime declaration and all lines containing it (except for the MsgBox
line... just remove the section of the output dealing with it for that
one).
The time it reports is only one of possibly many, many times where the
maximum is sure to occur, so it is kind of a meaningless entity to
display.
Removing it will make the loops it is in faster as the program won't have
to
waste time performing calculations involving it.
Rick
Less than one minute? Really?
Less than one minute? Really? Wow, I am surprised. I just added up all the
frequencies and it totals 24,881,557 one-second intervals that were
examined. I would have thought that many loops (even though all they are
actually doing is incrementing values by one most of the time), plus the
original 250,000 loops for your records, would have taken a lot, lot, lot
longer to run. Anyway, I'm glad the code worked out for you. Just a
reminder, don't forget, if you want to actually plot the number of lines
per
second, you can use the Seconds array for that (I'm guessing you saw my
other post to Ivan where I mentioned that as you responded to the message,
although not regarding its content).
Rick
Actually, in thinking about it, while there were 24,881,557 one-second
Actually, in thinking about it, while there were 24,881,557 one-second
intervals in your time span, 15,515,978 were not in use which means there
was no code activity for them. Hence, only 9,365,579 intervals were
actively
being incremented while the code was creating the frequency summary.. a
lot,
to be sure, but significantly less than 24,881,557. Still, less than a
minute to execute it all is still surprising to me.
Rick
message
See inline...No correction necessary, you are right...
See inline...
No correction necessary, you are right... the "times" are one-second
intervals in which that number of lines were in use (those one-second
intervals are not necessarily anywhere near each other, though). I thought
you might have wanted to plot out the results to see "when" the various
(descending) maximums were taking place (every Monday morning, just after
lunch, or some such statistic).
As long as they cover the same time frame, there shouldn't be any problem.
I
might wonder about when everything could slow, or break, down if you
started
gang consecutive years one after the other (say, a 10-year span)... that
might start to load down everything (but trust me, I do not know that for
sure at all... the code might survive that as well).
My pleasure... I really glad it worked out so well for you.
Rick
While I'm sure you understand this correctly, let me clarify/expand on
what I
While I'm sure you understand this correctly, let me clarify/expand on
what
I just said above. Let's look at the first summary line returned from you
running my code...
"There were 15 times 18 lines were in use at the same time."
That means out of 24,881,557 one-second intervals covered by the "time
span"
from your first to your last record, there were 15 individual one-second
intervals when 18 lines were in use at the same time. Those 15 one-second
intervals *might* have all been next to each other which would mean that
throughout your time span, 18 lines would have been in use only "once". On
the other hand, those 15 individual one-second intervals might be totally
isolated from each other and scattered haphazardly throughout the time
span.
There is no way of telling without some further analysis (this is where a
database would be useful as you could construct various queries to search
for underlying patterns or structure to the occurrences).
Rick
All very good advise - Gary's Student - as you point out, I am tryingto
avoid
All very good advise - Gary's Student - as you point out, I am trying
to avoid doing a time based analysis since it doesn't tell me how many
lines were overlapping (30 2 second calls could tie up anywhere from 1
line to 30). We have a software package that does trunk analysis, but
it does the same thing - how 'utilized' is each individual trunk on an
hourly basis...
My end goal would be to have a number for each row that show how many
lines are currently being used at that instant. Here is my initial
thought process (it is of course not right, but I'm hoping it will be
the building blocks to a possible solution):
Start Duration End 1 2 3 4 5 6 7
3/19/07 12:25:12 AM 73 3/19/07 12:26:25 AM 0 0 0 0 0 0 0
in E2 - I have the following formula:
=IF($C2>=INDIRECT("A" & ROW(C2)+E$1),1,0)
I then copied this formula to the right about 40 colums and down the
entire range of my spreadsheet. This then shows me (line by line) how
many calls the current call spans.
in AR2 - I have:
=SUM(INDIRECT("E"&ROW(AT2)&":E"&ROW(AT2)+SUM(E2:AQ2))) - this will
basically take and sum the 1s in column E starting with AR2 and going
down how ever many calls this call sapns (which in this example is 0)
- but longer duration calls will obviously span more calls.
I will copy a few thousand of the call records and upload it to a
website in the event anyone wants to actually see this spreadsheet. I
find this problem facinating, It seems so simple at first glance...but
it is certainly one of the hardest (if no the hardest) real life excel
crunching scenario I have seen.
Thanks again for your skills.
Nathaniel
Hi,I might be on the wrong track all together, but this looks like
anAccess
Hi,
I might be on the wrong track all together, but this looks like an
Access problem to me. How I tackled it (and I know there are better
ways in Access, but it's late and I'm not thinking clearly) was as
follows:
Table 1: (Named "Sheet1")
Start Duration End
19/03/2007 6:39:59 AM 143 19/03/2007 6:42:22 AM
19/03/2007 6:45:07 AM 9 19/03/2007 6:45:16 AM
19/03/2007 6:54:25 AM 14 19/03/2007 6:54:39 AM
etc.
(Duration column is redundant)
Table 2: (Named "Sheet2")
Time
19/03/2007 6:00:00 AM
19/03/2007 6:01:00 AM
19/03/2007 6:02:00 AM
19/03/2007 6:03:00 AM
19/03/2007 6:04:00 AM
19/03/2007 6:05:00 AM
19/03/2007 6:06:00 AM
etc.
Query1:
SELECT "Data" AS Data, Sheet2.Time, Sum(IIf([time] Between [Start] And
[End],1,0)) AS Engaged
FROM Sheet2, Sheet1
GROUP BY "Data", Sheet2.Time;
This gives you a list of times showing a row for every second. In the
engaged column, there will be a number representing the total number
of lines in use for this second. From here, there are two options:
1. Switch to PivotChart View
Drag the "Engaged" field to the data area
Drag an appropriate time interval to the category axis (I chose
Hours)
2. Build a Crosstab from this query:
TRANSFORM Sum(Query1.Engaged) AS SumOfEngaged
SELECT Query1.Data
FROM Query1
GROUP BY Query1.Data
PIVOT Format(Query1.Time,"hh");
This will give you a summarised table that you can then chart with
Excel, showing the total phone minutes in each hour.
Cheers,
Ivan.
On Mar 29, 11:33 pm, Gary''s Student
Hi Nathaniel,Reading your reply to Gary's Student, I changed Table 2 to
Hi Nathaniel,
Reading your reply to Gary's Student, I changed Table 2 to be
intervals of 1 second rather than 1 minute which will avoid the
problem of two calls placed in the same window that don't actually
overlap. You could then still run the crosstab query, but instead of
the sum function change to the max function. This would then give you
a profile of the maximum number of simultaneous calls in any specified
interval. From this analysis, your busiest period in the data you
provided was 10 simultaneous calls at 8:33am. The only issue I can see
here is that the 'Between' function may have to be replaced with a
different formula that will check for "Between or equal to" just to
catch those one or two calls that are less than three seconds.
Cheers,
Ivan.
and
th some
t the
nto
n
D1 and
0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
1
=A0 1
=A0 1
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
0
=A0 0
0
0
=A0 0
=A0 0
0
0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
0
=A0 0
0
0
0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
0
0
=A0 0
=A0 0
0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
0
0
=A0 0
e with
y not
y
...
Just out of interest (to see it it agrees with anyone else's results)here
it
Just out of interest (to see it it agrees with anyone else's results)
here it the data summary that I ended up with:
Max of Engaged
Hours Time Total
6 AM :39 1
:40 1
:41 1
:42 1
:43 0
:44 0
:45 1
:46 0
:47 0
:48 0
:49 0
:50 0
:51 0
:52 0
:53 0
:54 1
:55 0
:56 0
:57 0
:58 0
:59 0
7 AM :00 0
:01 0
:02 0
:03 1
:04 2
:05 0
:06 0
:07 1
:08 0
:09 0
:10 1
:11 1
:12 3
:13 1
:14 1
:15 1
:16 0
:17 0
:18 0
:19 0
:20 0
:21 0
:22 0
:23 0
:24 0
:25 0
:26 0
:27 0
:28 0
:29 0
:30 0
:31 0
:32 0
:33 0
:34 0
:35 0
:36 0
:37 1
:38 1
:39 0
:40 0
:41 0
:42 1
:43 1
:44 1
:45 1
:46 0
:47 0
:48 0
:49 0
:50 0
:51 0
:52 0
:53 1
:54 1
:55 1
:56 1
:57 1
:58 0
:59 1
8 AM :00 3
:01 3
:02 2
:03 1
:04 1
:05 1
:06 0
:07 0
:08 0
:09 0
:10 1
:11 0
:12 1
:13 1
:14 5
:15 2
:16 1
:17 0
:18 2
:19 2
:20 3
:21 4
:22 5
:23 5
:24 4
:25 5
:26 5
:27 5
:28 5
:29 5
:30 5
:31 5
:32 9
:33 10
:34 9
:35 8
:36 8
:37 7
:38 4
:39 5
:40 5
:41 5
:42 3
:43 3
:44 2
:45 2
:46 2
:47 2
:48 2
:49 2
:50 2
:51 1
:52 1
:53 1
:54 1
:55 1
:56 1
:57 1
:58 0
:59 0
9 AM :00 0
Grand Total 10
on and
with some
lit the
into
in
In D1 and
r:
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 1
=A0 1
=A0 1
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
=A0 0
nue with
may not
by
&b...
he
I've just gotten back to my house.
I have just gotten back to my house. I am getting ready to run Rick's
code - I will keep you posted on the progress...
just started running the code - I'm getting an overflow error with
just started running the code - I'm getting an overflow error with the
timeserial function - it apparently dies out at 32,767 - I will modify
the code to divide this out put it into hour minutes once it gets that
large...I'll check back once I change this
Re: Calculate actual trunk utilization based on call start/duration
The entire thing took less than 1 minute to run - here are the
results:
"There were 15 times 18 lines were in use at the same time.
There were 120 times 17 lines were in use at the same time.
There were 396 times 16 lines were in use at the same time.
There were 1238 times 15 lines were in use at the same time.
There were 4113 times 14 lines were in use at the same time.
There were 10571 times 13 lines were in use at the same time.
There were 24887 times 12 lines were in use at the same time.
There were 54191 times 11 lines were in use at the same time.
There were 112575 times 10 lines were in use at the same time.
There were 213338 times 9 lines were in use at the same time.
There were 361482 times 8 lines were in use at the same time.
There were 561049 times 7 lines were in use at the same time.
There were 773832 times 6 lines were in use at the same time.
There were 938616 times 5 lines were in use at the same time.
There were 993990 times 4 lines were in use at the same time.
There were 932789 times 3 lines were in use at the same time.
There were 1085005 times 2 lines were in use at the same time.
There were 3297372 times 1 lines were in use at the same time.
There were 15515978 times 0 lines were in use at the same time.
Rick - that was beautiful - it would have taken me forever to get
there without your help. Thanks to everyone that contributed!
Nathaniel Watkins
Garrett County Government
I was surprised as well - I assumed it would take hours to run...
I was surprised as well - I assumed it would take hours to run...plus
I'm doing this on one of our ultra-portables, so it isn't the fastest
machine in the world to start with. My main concern with the whole
plotting concept was to get an idea how often the lines were being
utilized. The printout works even better, as it shows how many
"times" (aka - how many seconds) each line was utilized (correct me if
I'm wrong). I couldn't have asked for a better solution. I am
planning on pulling out the same data from one of our other phone
switches and combining it with this one. That will give us a very
accurate representation on how many trunks we actually need in order
to support both locations. Thanks again for this solution.
Nathaniel
I just timed it - 16 seconds...
I just timed it - 16 seconds... and I did not remove the Maxtime
variable/calculations...
Sorry for the late response.
Sorry for the late response. I see the other responses have
calculated usage across fixed times. This macro counts the calls
active at the beginning of a call. It marks each call as Active, then
clears the flag as it ends. To do that, it must keep track of the
greatest previous end time. The data is pulled into an array for fast
processing.
The start and end times need to be in General format, so to retain the
original data, I copied the values to other columns manually.
Hopefully the comments help. I haven't been able to ftp your large
file for testing with 2007, but the only question was whether the
array would go that large, and others checked that in previous
replies.
The spaces constant and debug.prints can be removed when you are happy
with the functionality.
Carl Hartness
Sub CountActiveCalls()
' Carl Hartness 3/31/08
' column A: start date & time in date and time format
' column B: duration in seconds
' column C: end date & time in date and time format
'
' columns A, C are copied to E, F for general format
' column E: start date & time in general format
' column F: end date & time in general format
' column G: Active or blank
' column H: calls active at the beginning of this call
'
' macro pulls columns E & F into array, adds 2 columns for
' temporary Active flag and count for that call
Dim ary As Variant
Dim x&, y&, cnt& ' as long
Dim greatestPrevEnd# ' as double
Const spaces$ =3D " "
With Range("E5")
.CurrentRegion.NumberFormat =3D "General"
' load array
ary =3D .CurrentRegion
Debug.Print "array size "; UBound(ary, 1), UBound(ary, 2)
' if needed, add columns 3 & 4 to array
' call active during this call
' calls active during this call
If UBound(ary, 2) =3D 2 Then ReDim Preserve _
ary(1 To UBound(ary, 1), 1 To UBound(ary, 2) + 2)
greatestPrevEnd# =3D 0
cnt& =3D 0
For x& =3D 1 To UBound(ary, 1)
cnt& =3D cnt& + 1
Debug.Print Left(spaces, 6 - Len(CStr(x&))) & x&; " "; _
Round(ary(x&, 2), 5) & Left(spaces, 13 - Len(CStr(Round(ary(x&, 2),
5)))); _
Round(greatestPrevEnd#, 5) & Left(spaces, 13 -
Len(CStr(Round(greatestPrevEnd#, 5)))); _
"+ "; cnt&
' advance greatest previous end time if needed
If greatestPrevEnd# < ary(x&, 2) Then _
greatestPrevEnd# =3D ary(x&, 2)
' this call always active during this call
ary(x&, UBound(ary, 2) - 1) =3D "Active"
' decrement count for each active call ending before
' this call and before greatest previous end
For y& =3D 1 To x& - 1
If ary(y&, UBound(ary, 2) - 1) =3D "Active" _
And ary(y&, 2) < ary(x&, 1) _
And ary(y&, 2) < greatestPrevEnd# Then
' call ends during this call
cnt& =3D cnt& - 1
ary(y&, UBound(ary, 2) - 1) =3D ""
Debug.Print Left(spaces, 9 - Len(CStr(y&))) & y&; " "; _
Round(ary(y&, 2), 5) & Left(spaces, 13 - Len(CStr(Round(ary(y&, 2),
5)))); _
Round(greatestPrevEnd#, 5) & Left(spaces, 13 -
Len(CStr(Round(greatestPrevEnd#, 5)))); _
"- "; cnt&
End If
Next y&
ary(x&, UBound(ary, 2)) =3D cnt&
Next x&
Range(.Cells, .Cells(UBound(ary, 1), UBound(ary, 2))) =3D ary
End With
End Sub ' CountActiveCalls
On Mar 29, 12:07=A0am, (e-mail address removed) wrote:
I like to leave some blank rows at the top, so I start the data on row5...
I like to leave some blank rows at the top, so I start the data on row
5...
=A0 =A0 =A0 =A0 =A0 =A0 "
re
" _
ary
Submitted via EggHeadCafe - Software Developer Portal of Choice
Putting Twitter Realtime Search to Work
http://www.eggheadcafe.com/tutorial...24-c9960b55b669/putting-twitter-realtime.aspx
