Calculating Half-Hour Range for Hour-Minute Data

A

Anonymous

I am trying to automate the work to put a graph
together. Basically I have some issues reported in one
column and the time (in HH:MM format -- 9:00 AM) each
issue was reported in another column.

The problem is that I want a chart that displays how many
issues were reported in half hour increments (e.g., from
9:00 AM to 9:30 AM, etc.). Right now I am doing this
manually my creating another column with the half-hour
increments and basing my chart on this manually created
column. Basically if I had two values in this range in
my source column (9:15 AM and 9:22 AM) I then put the
value 2 in my manual column to represent the amount of
issues reported between 9:00 and 9:30. My chart uses the
column with "2" in it.

Ideally, I would just be able to use a formula to count
all values from the column that are in a particular half-
hour increment. Since COUNTIF and SUMIF only appear to
allow for one condition, I don't see how I can do this.
Does anyone have any suggestions for how to automatically
count how many values from a column fall within a half-
hour range?

Thanks.
 
B

Bernie Deitrick

Anon,

Please don't mulit-post - we read most of the groups anyway, and this just
makes for more work. Here is my reply to your other post:

With your times in column A, put the start time for each time segment
starting in C2, and continuing down the column.

Then in cell D2, use the formula

=COUNTIF(A:A,">="&C2)-COUNTIF(A:A,">="&C3)

and copy down to match your times in column C.

HTH,
Bernie
MS Excel MVP
 

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

Top