Times and Frequency?

[Jim]

Thanks for reading this post...
I have some data from a hospital that I need to analyze.
Column A contains the time a surgery started
Column B contains the time the surgery ended.

A B
8:24 9:35
8:32 9:50
8:45 10:31
etc. etc.

Now, I want to be able to determine how many surgeries
were in progress at:
8:00
8:30
9:00
9:30
10:00
etc.

Any help would be greatly appreciated.
THANK YOU!
Jim

 

[Frank Kabel]

Hi
lets say your input data is on sheet1 and you have in column a on
sheet2 your starting times.
Enter the following formula in B1 on this second sheet:
=SUMPRODUCT(--('sheet1'!$A$1:$A$100<A2),--('sheet1'!$B$1:$B$100>=A1))

Note: this formula does not work if your schedules span midnight

 

[Guest]

Frank, once again, you are my hero...
What is the significance of the "--" in the formula?
Thanks,
Jim

 

[Frank Kabel]

Hi
see for an explanation:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
and
http://www.mcgimpsey.com/excel/formulae/doubleneg.html