Histogram of times

**bradles** · 03-09-2010, 08:55 AM

I have sleep data that shows the start and finish times of when a person is asleep over a number of different days.

Example
10:00 to 10:03
10:01 to 10:02
13:00 to 13:02
etc
etc

I'd like to produce a histogram (in 1 minute intervals) showing what times during the day that the person sleeps the most.

For the above example the bins should look like the following:

00:00 = 0
etc
etc
10:00 = 1
10:01 = 2
10:02 = 2
10:03 = 1
10:04 = 0
etc
etc
13:00 = 1
13:01 = 1
13:02 = 1
13:03 = 0
etc
etc

Does anyone know of a way of producing a histogram like this when dealing with start/finish time sets?

Brad

**zbor** · 03-09-2010, 09:28 AM

What version of excel do you use?

**bradles** · 03-09-2010, 09:31 AM

sorry...forgot to mention that I am using Excel 2003.

**zbor** · 03-09-2010, 10:05 AM

Is this it?

**bradles** · 03-09-2010, 10:28 AM

That looks about right.

What do the -- signs do in the SUMPRODUCT formula?
=SUMPRODUCT(--($A$2:$A$1000<E7),--($B$2:$B$1000>=E7))

**zbor** · 03-09-2010, 10:32 AM

To SUMPRODUCT nothing... to other parts:

--($A$2:$A$1000<E7)

coverts TRUE/FALSE into 1/0

example: you have 1,2,3,4,5

--(A1:A5>3)
--(FALSE, FALSE, FALSE, TRUE, TRUE)
0,0,0,1,1