Group Minutes In Defined Intervals

[kumara_faith]

Hi,

I have the following data:

Excel Workbook
	K
1	Wait Period
2	0:00:42
3	0:00:11
4	0:00:57
5	0:00:21
6	0:05:52
7	0:06:04
8	0:11:45
9	0:20:57
10	0:00:12
11	0:00:28
12	0:01:22
13	0:10:29
14	0:21:23
15	0:00:15
16	0:04:53
17	0:03:21
18	0:03:15
19	0:10:33
20	0:00:17
21	0:00:09
22	0:04:05
23	0:08:01
24	0:12:27
25	0:12:45
26	0:21:09
27	0:21:40
28	0:21:08
29	0:29:20
Data

I need to group the above data in intervals of 0-5,5-10,10-15,15-20 and above 30 minutes. If the waiting time is 3 minutes, the formula should return "0-5". Is there a way to achieve this via formula?

 

[mrhstn]

Your quickest method would be to create a table and lookup the interval

For example

Book1
	Q	R
1	00:00:00	0-5
2	00:05:00	5-10
3	00:10:00	10-15
4	00:15:00	15-20
5	00:20:00	20-30
6	00:30:00	30+
Sheet1

Then use a simple formula

Book1
	K	L
1	Wait Period	Interval
2	00:00:42	0-5
3	00:00:11	0-5
4	00:00:57	0-5
5	00:00:21	0-5
6	00:05:52	5-10
7	00:06:04	5-10
8	00:11:45	10-15
9	00:20:57	20-30
10	00:00:12	0-5
11	00:00:28	0-5
Sheet1

Cell Formulas
Range	Formula
L2	=VLOOKUP(K2, $Q$1:$R$5, 2, 1)

 

[kumara_faith]

Hi,

I have tried but the results are as follows:

Excel Workbook
	K	L	M	N	O	P
1	Wait Period	Range		Range	Results
2	0:00:42	#N/A		0:00:00	0-5
3	0:00:11	#N/A		0:05:00	5-10
4	0:00:57	#N/A		0:10:00	10-15
5	0:00:21	#N/A		0:15:00	15-20
6	0:05:52	#N/A		0:20:00	20-30
7	0:06:04	#N/A		0:30:00	>30
8	0:11:45	#N/A
9	0:20:57	#N/A
10	0:00:12	#N/A
11	0:00:28	#N/A
12	0:01:22	#N/A
13	0:10:29	#N/A
14	0:21:23	#N/A
15	0:00:15	#N/A
16	0:04:53	#N/A
17	0:03:21	#N/A
Data

The result in column L in #N/A. Please assist.

 

[mrhstn]

If you look at my formula the last parameter is set to 1 (TRUE), not 0 (FALSE). False will return an exact match, true will return the nearest lower value.