Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|