COUNTIFS function w/greater than or equal to, and less than or equal to time values

[AliciaRenee]

Hello,

I am new to this forum and am just beginning to explore all the functions of Excel 2007 (so my terminology and understanding may not be up to par).

I am currently creating a workbook that will pull data from patient sign in times to see how long their wait time was, to improve our offices efficiency.

I want to count patients at different time intervals to see which step in our prep process takes the longest:
0-3 minutes, 4-6 minutes, 7-9 minutes, and >10 minutes

Example; The formula that I have in my cell is: =COUNTIFS(E2:E18,">=0:04",E2:E18,"<=0:06") which pulls 2 patients. However, when manually counting the data, the total amount of patients should be 3

Is there another formula I should be using?

I also tried: =COUNTIFS(C2:C18,">0:03",C2:C18,"<0:07") but that is counting the data for 3 minutes and 7 minutes, which I don't want.

If you view the attachment: The data and formulas that I have in the total wait time section works perfectly with the same formulas described above.

Very confused here,
Thanks

[Jonmo1]

Try
=COUNTIFS(C2:C18,">=0:03",C2:C18,"<=0:07")

[AliciaRenee]

It's pulling the 0:03 and 0:07 to the count (which is how it should be).

I just want 4-6 minutes, which was what I was trying to get out of the first formula but it's not counting the 0:04's

[Jonmo1]

OK, I think we have a floating point precision problem.
Google it, it's a documented issue with how decimals are stored..

Basically your result of 0:04 in C6 is not EXACTLY 4 minutes due to the precision issue.
You can see this with
=C6="0:04"+0
That returns FALSE

Need to round the results of the time subtraction.

Try changing the formula in C2:C18 to
=MROUND(B2-A2,"0:01"+0)
This rounds the results to the nearest minute.

Same for column F and H

[AliciaRenee]

I'm still looking into what you call the floating point precision problem.

I did put in the formula into C2:C18 and it is no longer giving me time values. 4 values read 0.01 and the rest are 0

[Jonmo1]

Sorry, you have to format the cells as a Time ... h:mm


It'll help if you include the word Excel in your google search.
Look at this
http://support.microsoft.com/kb/78113

[AliciaRenee]

I formatted the minute columns to time; h:mm and it seems to have worked!

I was unaware of that issue, so thanks for that reference!

Everything seems good =)

[Jonmo1]

Glad to help, thanks for the feedback.