statistics

  • Thread starter Thread starter Diana
  • Start date Start date
D

Diana

hi, I'm using excel for statistics. I have cases I work
and I write the times in column a. The column is formated
to a time format. I want to know if there is anyway to
count ranges on times. For example I need to know how
many instances there are of cases that fall between 1-3pm
and such. Anyone know a formula I can use.
 
One way:

=SUMPRODUCT(--(A1:A1000>=TIME(13,0,0)),--(A1:A1000<=TIME(15,0,0)))

another:

=COUNTIF(A:A,">="&TIME(13,0,0)) - COUNTIF(A:A,">"&TIME(15,0,0))

Note that these formulas assume "between" to mean inclusive (i.e.,
cases at 1:00 pm and 3:00 pm should both be counted). If exclusive:

=SUMPRODUCT(--(A1:A1000>TIME(13,0,0)),--(A1:A1000<TIME(15,0,0)))

and

=COUNTIF(A:A,">"&TIME(13,0,0)) - COUNTIF(A:A,">="&TIME(15,0,0))
 

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

Back
Top