Distribution in time

  • Thread starter Thread starter acces
  • Start date Start date
A

acces

Hi, I can't find solution for following excel problem:
In one table I have two columns - start date and end date (look lik
this):

Start End
1.1.2003 14:53 1.1.2003 15:06
1.1.2003 14:54 1.1.2003 15:08
... about 10 000 entries

In the second table I would like to have two columns too:
Minute Count
1.1.2003 14:53 1
1.1.2003 14:54 2
1.1.2003 14:55 2
...
1.1.2003 15:06 2
1.1.2003 15:07 1
1.1.2003 15:08 1
1.1.2003 15:09 0,
...thousands of entries

How can I coun't number of entries:
a] this minute is Start date +
b] this minute is between Start and End +
c] this minute is End,
using one formula?

I hope someone finds useful way..
 
Hi
if on your second sheet in column A is the start time you're
interested in, put the following formula in cell B1:
=SUMPRODUCT(--('sheet1'!$A$1:$A$10000>=A1),--('sheet1'!$A$1:$A$10000<=A
2))+SUMPRODUCT(--('sheet1'!$A$1:$A$10000<A1),--('sheet1'!$B$1:$B$10000>
=A1))

Note: this counts entries twice which start at the end time. So you may
use:
=SUMPRODUCT(--('sheet1'!$A$1:$A$10000>=A1),--('sheet1'!$A$1:$A$10000<

copy either of these formulas
downA2))+SUMPRODUCT(--('sheet1'!$A$1:$A$10000<A1),--('sheet1'!$B$1:$B$1
0000>=A1))
 
Back
Top