Excel formula - Anyone up for a challenge?

N

NEHicks

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
 
L

Luke M

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.
 
N

NEHicks

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, ...
 
D

Daryl S

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.
 
M

Minty Fresh

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Results don't macth. 2
Determine if night shift by start & end time 2
Average Time for Specific Item 10
Time formula Needed 6
Total percentages 2
Challenge for a formula 4
Quarter Hour time conversions for timesheet 2
Difference in Time hour what formula to use in excel? 4

Top