Times and Frequency?
 

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Jim" schrieb im Newsbeitrag
...
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, once again, you are my hero...
What is the significance of the "--" in the formula?
Thanks,
Jim

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Jim" schrieb im
Newsbeitrag
...
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



.

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

--
Regards
Frank Kabel
Frankfurt, Germany

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Jim" schrieb im
Newsbeitrag
...
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



.