|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 7th, 2009, 05:20 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula - Anyone up for a challenge?
I need to provide the percentage by hour per month of hospital discharges.
Discharge data is kept by date then by hour of discharge. Each day could have 1 or more discharges during the hours of 8:00 am and 8:00 pm. I can find the percentage using a range, i.e., 8-noon using a countif forumla, but was hoping not to have to create a formula for each hour. I can sort by date and time or by just time to eyeball the percentage, but I know that won't be acceptable. Any ideas of formulas to use would be appreciated. Thanks 
|
|
#2
December 7th, 2009, 05:31 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel formula - Anyone up for a challenge?
Let's say you have a date entered in cell A2 to tell us which month you want.
A3 is the start time, A4 is the end time. Your data is on Sheet2. The formula is: =SUMPRODUCT(--(TEXT('Sheet2'!A2:A100,"mmyyyy")=TEXT(A2,"mmyyyy") , --('Sheet2'!B2:B100=A3),--('Sheet2'!B2:B100=A4))/SUMPRODUCT(--(TEXT('Sheet2'!A2:A100,"mmyyyy")=TEXT(A2,"mmyyyy") ) Format as percentage. This should give the ratio of how many discharges in that time slot of a specific month compared to the total month. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NEHicks" wrote: I need to provide the percentage by hour per month of hospital discharges. Discharge data is kept by date then by hour of discharge. Each day could have 1 or more discharges during the hours of 8:00 am and 8:00 pm. I can find the percentage using a range, i.e., 8-noon using a countif forumla, but was hoping not to have to create a formula for each hour. I can sort by date and time or by just time to eyeball the percentage, but I know that won't be acceptable. Any ideas of formulas to use would be appreciated. Thanks
|
|
#3
December 7th, 2009, 05:50 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel formula - Anyone up for a challenge?
I don't need to compare to another month, just get a percentage for one month
at a time. Such as for November 2009 - there were 458 discharges. How many discharged between 8-9, 9-10, 10-11, etc. By just looking at the data, 8 discharged between 8 and 9, 21 between 9 and 10, ... "Luke M" wrote: Let's say you have a date entered in cell A2 to tell us which month you want. A3 is the start time, A4 is the end time. Your data is on Sheet2. The formula is: =SUMPRODUCT(--(TEXT('Sheet2'!A2:A100,"mmyyyy")=TEXT(A2,"mmyyyy") , --('Sheet2'!B2:B100=A3),--('Sheet2'!B2:B100=A4))/SUMPRODUCT(--(TEXT('Sheet2'!A2:A100,"mmyyyy")=TEXT(A2,"mmyyyy") ) Format as percentage. This should give the ratio of how many discharges in that time slot of a specific month compared to the total month. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NEHicks" wrote: I need to provide the percentage by hour per month of hospital discharges. Discharge data is kept by date then by hour of discharge. Each day could have 1 or more discharges during the hours of 8:00 am and 8:00 pm. I can find the percentage using a range, i.e., 8-noon using a countif forumla, but was hoping not to have to create a formula for each hour. I can sort by date and time or by just time to eyeball the percentage, but I know that won't be acceptable. Any ideas of formulas to use would be appreciated. Thanks
|
|
#4
December 7th, 2009, 06:04 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel formula - Anyone up for a challenge?
NEHicks -
It sounds like a pivot table will do what you want. Use the wizard to create it, and you can display either counts of discharges or the percents. Depending on how your data is stored, you may need to add a column to your data that will have a formula based on the discharge date that will give you just the month and hour of discharge. -- Daryl S "NEHicks" wrote: I need to provide the percentage by hour per month of hospital discharges. Discharge data is kept by date then by hour of discharge. Each day could have 1 or more discharges during the hours of 8:00 am and 8:00 pm. I can find the percentage using a range, i.e., 8-noon using a countif forumla, but was hoping not to have to create a formula for each hour. I can sort by date and time or by just time to eyeball the percentage, but I know that won't be acceptable. Any ideas of formulas to use would be appreciated. Thanks
|
|
#5
December 7th, 2009, 08:59 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel formula - Anyone up for a challenge?
That's what the formula does...are you getting an error of some type?
-- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NEHicks" wrote: I don't need to compare to another month, just get a percentage for one month at a time. Such as for November 2009 - there were 458 discharges. How many discharged between 8-9, 9-10, 10-11, etc. By just looking at the data, 8 discharged between 8 and 9, 21 between 9 and 10, ... "Luke M" wrote: Let's say you have a date entered in cell A2 to tell us which month you want. A3 is the start time, A4 is the end time. Your data is on Sheet2. The formula is: =SUMPRODUCT(--(TEXT('Sheet2'!A2:A100,"mmyyyy")=TEXT(A2,"mmyyyy") , --('Sheet2'!B2:B100=A3),--('Sheet2'!B2:B100=A4))/SUMPRODUCT(--(TEXT('Sheet2'!A2:A100,"mmyyyy")=TEXT(A2,"mmyyyy") ) Format as percentage. This should give the ratio of how many discharges in that time slot of a specific month compared to the total month. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NEHicks" wrote: I need to provide the percentage by hour per month of hospital discharges. Discharge data is kept by date then by hour of discharge. Each day could have 1 or more discharges during the hours of 8:00 am and 8:00 pm. I can find the percentage using a range, i.e., 8-noon using a countif forumla, but was hoping not to have to create a formula for each hour. I can sort by date and time or by just time to eyeball the percentage, but I know that won't be acceptable. Any ideas of formulas to use would be appreciated. Thanks
|
|
#6
December 7th, 2009, 10:21 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel formula - Anyone up for a challenge?
Excel 2007 PivotTable
As suggested by Daryl... http://www.mediafire.com/file/ilmmoy...12_06_09c.xlsx
|
|
#7
December 8th, 2009, 11:47 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel formula - Anyone up for a challenge?
Hi NE
I can invision a number of ways to do this, but I need an example of what the data entry is going to look like. "NEHicks" wrote: I need to provide the percentage by hour per month of hospital discharges. Discharge data is kept by date then by hour of discharge. Each day could have 1 or more discharges during the hours of 8:00 am and 8:00 pm. I can find the percentage using a range, i.e., 8-noon using a countif forumla, but was hoping not to have to create a formula for each hour. I can sort by date and time or by just time to eyeball the percentage, but I know that won't be acceptable. Any ideas of formulas to use would be appreciated. Thanks
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
