CountIF

[Brendon]

I want to do a count of patients that are divided up into two groups.

Patients in the morning (before 11:59) that waited more than 20 mins
Patients in the afternoon (after 11:59) that waited more than 20 mins

Here's what I have, but it doesn't seem to calculate right.
=IF(E:E,"<0.5",COUNTIF(H:H,">" & TIME(0,20,0)))
which gives me <0.5

I'm missing something, but I can't figure it out.

Brendon

 

[T. Valko]

You're creating a "hole" at 12 pm:

before 11:59
after 11:59

Here's what you asked for:

=SUMPRODUCT(--(E1:E100<0.5),--(H1:H100>TIME(0,20,0)))

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007.

 

[Bernard Liengme]

You have not clearly told us what data you have in columns E and H.
If column E has real times (like 8:00) and H has a number (like
12)representing minutes waited:
=SUMPRODUCT(--(E1:E100<TIME(12,0,0)), --(H1:H100>20))
Do not use full columns
=SUMPRODUCT(--(E:E<TIME(12,0,0)), --(H:H>20))
unless you have Excel2007
best wishes

 

[Brendon]

OK, this worked, thank you.

Two other questions.

I changed the E:E to E2:E2000 and H:H to H2:H2000
I'm doing this via VB, is there a way to declare a variable (or two)
at the beginning and then just change the variable and have it change
the variable in the code so that if I wanted to go from 2:2000 to say
2:5000, I just update the variable.

Also, you said there was a whole, will the below code, fix that hole?

Brendon

 

[T. Valko]

You'd have to post the code. I'd recommend you post it in the programming
forum. That's where all the programmers hangout!

You're creating a "hole" at 12 pm:
before 11:59
after 11:59

Actually, the "hole" is at 11:59 but that's easily fixed. Just change the
test. Maybe use <=0.5 which makes the boundary 12 PM. And then, for the
"other end" use >0.5.

--
Biff
Microsoft Excel MVP


OK, this worked, thank you.

Two other questions.

I changed the E:E to E2:E2000 and H:H to H2:H2000
I'm doing this via VB, is there a way to declare a variable (or two)
at the beginning and then just change the variable and have it change
the variable in the code so that if I wanted to go from 2:2000 to say
2:5000, I just update the variable.

Also, you said there was a whole, will the below code, fix that hole?

Brendon

 

[Bernard Liengme]

You showed a test for >11:59 and another for <11:59
Biff is talking about the 'hole' at exactly 12:00 - you are not capturing
any records with exactly that time
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

OK, this worked, thank you.

Two other questions.

I changed the E:E to E2:E2000 and H:H to H2:H2000
I'm doing this via VB, is there a way to declare a variable (or two)
at the beginning and then just change the variable and have it change
the variable in the code so that if I wanted to go from 2:2000 to say
2:5000, I just update the variable.

Also, you said there was a whole, will the below code, fix that hole?

Brendon

 

[Shane Devenshire]

Hi

You can also consider that since 20/60*24 = .01389... you can substitute
this approximation for the TIME(0,20,0) function. The plus side is one less
calculation and a shorter formula, the downside is the approximate nature and
lack of clarity.

=SUMPRODUCT(--(E1:E100<0.5),--(H1:H100>0.0139))

One of your two formulas should read <=0.5 or >=.5 as has been implied in
previous posts.

You can also replace TIME(0,20,0) with

TIME(,20,)

 

[Brendon]

OK, so I'll be more specific.

Column E has Patient Arrival Times (I'm using E2:E2000)
Column H has the amount of time from when they arrived at the office,
to when the technician started assisting them. (H2:H2000)

The formulas give me results greater than 1000, when there's only 640
patients. So I don't think it's calculating correctly.

Brendon

 

[T. Valko]

Try this:

=SUMPRODUCT(--(E2:E2000<>""),--(E2:E2000<0.5),--(ISNUMBER(H2:H2000)),--(H2:H2000>TIME(0,20,0)))


--
Biff
Microsoft Excel MVP


OK, so I'll be more specific.

Column E has Patient Arrival Times (I'm using E2:E2000)
Column H has the amount of time from when they arrived at the office,
to when the technician started assisting them. (H2:H2000)

The formulas give me results greater than 1000, when there's only 640
patients. So I don't think it's calculating correctly.

Brendon

 

[Brendon]

Biff - AWESOME!!!

Thank You.

Try this:

=SUMPRODUCT(--(E2:E2000<>""),--(E2:E2000<0.5),--(ISNUMBER(H2:H2000)),--(H2:H2000>TIME(0,20,0)))

--
Biff
Microsoft Excel MVP


OK, so I'll be more specific.

Column E has Patient Arrival Times (I'm using E2:E2000)
Column H has the amount of time from when they arrived at the office,
to when the technician started assisting them. (H2:H2000)

The formulas give me results greater than 1000, when there's only 640
patients.  So I don't think it's calculating correctly.

Brendon

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP


Biff - AWESOME!!!

Thank You.