Time series
 

I have several tabs for different dates with data in it. Colomn G
contains the time in format 13:30:30.

For my project, I require to know the number of data points in various
time series, pls see below.

7:30:00 8:30:00
8:30:00 9:30:00
9:30:00 10:30:00
10:30:00 11:30:00
11:30:00 12:30:00
12:30:00 13:30:00
13:30:00 14:30:00
14:30:00 15:30:00
15:30:00 16:00:00

I tried countif and if function but could not come up with the results,
PLS HELP. If I have to do it manually then I am dead......


--
mohitmahajan
------------------------------------------------------------------------
mohitmahajan's Profile: http://www.excelforum.com/member.php...fo&userid=8130
View this thread: http://www.excelforum.com/showthread...hreadid=492678

Time series
 

Do you mean something along the lines of

=SUMPRODUCT(--(G$2:$G$1000=TIME(7,30,0)),--(G$2:$G$1000<TIME(8,30,0)))

etc.

Best to put the comparison times in cells and use say

=SUMPRODUCT(--($G$2:$G$1000=A1),--($G$2:$G$1000<B1))

which can then be copied easily

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mohitmahajan"
wrote in message
...

I have several tabs for different dates with data in it. Colomn G
contains the time in format 13:30:30.

For my project, I require to know the number of data points in various
time series, pls see below.

7:30:00 8:30:00
8:30:00 9:30:00
9:30:00 10:30:00
10:30:00 11:30:00
11:30:00 12:30:00
12:30:00 13:30:00
13:30:00 14:30:00
14:30:00 15:30:00
15:30:00 16:00:00

I tried countif and if function but could not come up with the results,
PLS HELP. If I have to do it manually then I am dead......


--
mohitmahajan
------------------------------------------------------------------------
mohitmahajan's Profile:
http://www.excelforum.com/member.php...fo&userid=8130
View this thread: http://www.excelforum.com/showthread...hreadid=492678

Time series
 

No...what I am trying to do is find the number of times the data lies
between these two parameters. For example, if the data in G colomn is
like below:

Time
7:32:57
7:48:26
7:55:05
7:57:37
8:02:17
8:08:36
8:14:05
8:19:27
8:21:10
8:25:56
9:08:11
9:09:48
9:19:56
9:29:06
9:32:05
9:47:04
9:53:58
9:55:51
10:01:41
10:06:08
10:10:05
10:23:34
10:29:22
10:41:46
11:00:09
11:09:48
11:16:05
11:20:23
11:25:37
11:28:45
11:34:51
11:41:27
11:47:47
11:52:14

Now from this data I want to know how many lie in the series (as I am
calling it) or in the time groups specified.
7:30:00 to 8:30:00 - In this all data points greater than or equal to
7:30:00 and less than 8:30:00 are to be taken into count. Likewise for
rest of the series....


--
mohitmahajan
------------------------------------------------------------------------
mohitmahajan's Profile: http://www.excelforum.com/member.php...fo&userid=8130
View this thread: http://www.excelforum.com/showthread...hreadid=492678

Time series
 

That was what Bob's formula did, did you even try it? It returns 10 using
you sample, if not show the result you expect

--
Regards,

Peo Sjoblom

(No private emails please)


"mohitmahajan"
wrote in message
news:mohitmahajan.1zyl7n_1134444301.4349@excelforu m-nospam.com...

No...what I am trying to do is find the number of times the data lies
between these two parameters. For example, if the data in G colomn is
like below:

Time
7:32:57
7:48:26
7:55:05
7:57:37
8:02:17
8:08:36
8:14:05
8:19:27
8:21:10
8:25:56
9:08:11
9:09:48
9:19:56
9:29:06
9:32:05
9:47:04
9:53:58
9:55:51
10:01:41
10:06:08
10:10:05
10:23:34
10:29:22
10:41:46
11:00:09
11:09:48
11:16:05
11:20:23
11:25:37
11:28:45
11:34:51
11:41:27
11:47:47
11:52:14

Now from this data I want to know how many lie in the series (as I am
calling it) or in the time groups specified.
7:30:00 to 8:30:00 - In this all data points greater than or equal to
7:30:00 and less than 8:30:00 are to be taken into count. Likewise for
rest of the series....


--
mohitmahajan
------------------------------------------------------------------------
mohitmahajan's Profile:
http://www.excelforum.com/member.php...fo&userid=8130
View this thread: http://www.excelforum.com/showthread...hreadid=492678

Time series
 

:confused: Silly me....

All I had to do after putting in the formula by Bob was to change the
reference A1 and B1 to the ones I wanted and also change the
format.....

Well, its my fault....

Thanks both of you, you have been a great help....


--
mohitmahajan
------------------------------------------------------------------------
mohitmahajan's Profile: http://www.excelforum.com/member.php...fo&userid=8130
View this thread: http://www.excelforum.com/showthread...hreadid=492678

Time series
 

Ok, here is the 2nd part of the problem.....

Now I have been asked to take out data per associate hour wise.....
I have attached the sheet with the table also in which info is
required....

Pls help:confused:


+-------------------------------------------------------------------+
|Filename: Book2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4117 |
+-------------------------------------------------------------------+

--
mohitmahajan
------------------------------------------------------------------------
mohitmahajan's Profile: http://www.excelforum.com/member.php...fo&userid=8130
View this thread: http://www.excelforum.com/showthread...hreadid=492678

Time series
 

I tried :

=+COUNTIF(*$A$2*:$A$664,AND($A$2,SUMPRODUCT(--($G$2:$G$1000=K2),--($G$2:$G$1000<L2))))

but this did not work even though it did not give me any error. The
value returned here for all time series/groups was 0.
The cell in bold is the name reference....

Pls help....


--
mohitmahajan
------------------------------------------------------------------------
mohitmahajan's Profile: http://www.excelforum.com/member.php...fo&userid=8130
View this thread: http://www.excelforum.com/showthread...hreadid=492678

Time series
 

As far as I can see, there are no names associated with the times, so you
cannot get an analysis by time by name.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mohitmahajan"
wrote in message
news:mohitmahajan.1zyzka_1134462901.2985@excelforu m-nospam.com...

I tried :


=+COUNTIF(*$A$2*:$A$664,AND($A$2,SUMPRODUCT(--($G$2:$G$1000=K2),--($G$2:$G$
1000<L2))))

but this did not work even though it did not give me any error. The
value returned here for all time series/groups was 0.
The cell in bold is the name reference....

Pls help....


--
mohitmahajan
------------------------------------------------------------------------
mohitmahajan's Profile:
http://www.excelforum.com/member.php...fo&userid=8130
View this thread: http://www.excelforum.com/showthread...hreadid=492678

Time series
 

:) Got it....
Now what I did was

=IF(AND(G244<$N$2,G244=$M$2),"7:30 till
8:30",IF(AND(G244<$N$8,G244=$M$8),"13:30 to
14:30",IF(AND(G244<$N$7,G244=$M$7),"12:30 to
13:30",IF(AND(G244<$N$6,G244=$M$6),"11:30 to
12:30",IF(AND(G244<$N$5,G244=$M$5),"10:30 to
11:30",IF(AND(G244<$N$4,G244=$M$4),"9:30 to
10:30",IF(AND(G244=$M$3,G244<$N$3),"8:30 to 9:30")))))))

I copied this and got each data point in a series and then did a pivot
on them and got hour wise time spent and hour wise data points for each
associate/team member......

Thanks Bob for your patience.....:)


--
mohitmahajan
------------------------------------------------------------------------
mohitmahajan's Profile: http://www.excelforum.com/member.php...fo&userid=8130
View this thread: http://www.excelforum.com/showthread...hreadid=492678