Count Criteria Between Range

[bloy.d]

Hi all,

Sorry for posting this I'm sure it's a noob question but spent hours with no joy. I firstly tired to do this in SQL but decided that's way beyond my capabilities so exported the data to Excel.

Baiscally i have a list of phone records that have a start time and an End time. I need to find out the peak concurrent calls for a given day.

To do this I've broken down the Excel to look like this: (HHMMSS)

StartTime EndTime
010001 010036
010007 010031
010030 010039


StartTime is an array called ST; EndTime is an array called ET

I also have a 3rd Array called TSLOT (TimeSlot); This is a list that ranges in 5 second incrments from 000000 to 235955

Ie

000000
000005
000010
000015

etc...

235950
235955



What I want to do is to count the number of times that each value within TSLOT appears between ST and ET and display the results like this

TSLOT - Count
010000 - 0
010005 - 1
010010 - 2
010015 - 2
010020 - 2
010025 - 2
010030 - 3
010035 - 2
010040 - 0


Any help to acheive this would be greatly appreciated.

Thanks
bloy.d

[NBVC]

How do come out with the counts in your example?

i..e How do you get 2 for 010010, etc.?

[bloy.d]

Hi NBVC

I get a count of 2 because their are 2 occurances of 010010 with the range >ST AND <ET.

Ie:

The first call:

Started at 010001
Ended at 010036

This means that 010010 falls within this range

the Second Call

Started at 010007
Ended at 010031

This means that 010010 falls within this range

the Third call however didn't start till 010030 and so 010010 dosn't fall within it's range

Confused.. me to!

Thus the number of times that 010010 is between ST and ET is = 2

I need formular to do this as I have Circa 250K calls per day

Thanks
Bloy.d

[NBVC]

Try:

=SUMPRODUCT(--(ST<=F2),--(ET>=F2)) copied down.

where F2 is the first TSLOT entry.

[bloy.d]

Thanks NBVC - Seesm to be looking good. fingers crossed!!