Identifying times from a list

[tourgis2000]

Hi,

I have a list of variables created by a user like this:

BAND ID START END
Band1 06:00 08:59
Band2 09:00 15:59
Band3 16:00 17:59
Band4 18:00 22:30
Band5 22:31 24:29
Band6 24:30 05:59

Seperately I have data from a third party like this:

DATE TIME VALUE
18/05/08 17:22 10
19/05/08 02:15 1
20/05/08 07:14 5
20/05/08 21:36 15
20/05/08 25:43 1
21/05/08 19:15 15
22/05/08 18:26 16
22/05/08 24:59 2
etc.

I need to be able to identify which BAND ID belongs to each TIME in the list so that I can sum the VALUE figures for each BAND ID (e.g. the first time given,17:22, would belong to Band 3). have tried using LOOKUP but I can't seem to get it to work. Additionally, I have a problem with the 30 hour clock. Would I have to stick to a 24 hour clock? This would mean that I would have to find each time from the list that was between 24:00 and 05:59 and allocate it to the previous day because it is important to keep the values that I ultimately derive in the right day of the week.

Thanks in advance,

Martin

 

[al_b_cnu]

Hi!

Would this suit, which avoids using a 30-hour clock:

Book1
	A	B	C	D	E	F	G	H
1	BAND_ID	START	END		DATE	TIME	VALUE
2	Band5	00:00	00:29		18/05/2008	17:22	10	Band3
3	Band6	00:30	05:59		19/05/2008	02:15	1	Band6
4	Band1	06:00	08:59		20/05/2008	07:14	5	Band1
5	Band2	09:00	15:59		20/05/2008	21:36	15	Band4
6	Band3	16:00	17:59		21/05/2008	01:43	1	Band6
7	Band4	18:00	22:30		21/05/2008	19:15	15	Band4
8	Band5	22:31	00:29		22/05/2008	18:26	16	Band4
9					23/05/2008	00:59	2	Band6
Sheet1

formuls in H2 copied down is =INDEX(A:A,MATCH(F2,B:B,1),1)

 

[gsbelbin]

Hi,

I have a list of variables created by a user like this:

BAND ID START END
Band1 06:00 08:59
Band2 09:00 15:59
Band3 16:00 17:59
Band4 18:00 22:30
Band5 22:31 24:29
Band6 24:30 05:59

Seperately I have data from a third party like this:

DATE TIME VALUE
18/05/08 17:22 10
19/05/08 02:15 1
20/05/08 07:14 5
20/05/08 21:36 15
20/05/08 25:43 1
21/05/08 19:15 15
22/05/08 18:26 16
22/05/08 24:59 2
etc.

I need to be able to identify which BAND ID belongs to each TIME in the list so that I can sum the VALUE figures for each BAND ID (e.g. the first time given,17:22, would belong to Band 3). have tried using LOOKUP but I can't seem to get it to work. Additionally, I have a problem with the 30 hour clock. Would I have to stick to a 24 hour clock? This would mean that I would have to find each time from the list that was between 24:00 and 05:59 and allocate it to the previous day because it is important to keep the values that I ultimately derive in the right day of the week.

Thanks in advance,

Martin

I think the best way to deal with the 30 hour clock is to take your times and multiply them by 24/30. Then to sum the values for each band you need to use a CSE equation like {=SUM((I2>$B$2:$B$7)*(I2<$C$2:$C$7)*G2)} where column I holds your converted times, B2 to B7 are your start times, C2 to C7 are your end times and G2 is the value for time in I2. Then copy down for the other rows. It seems to work for me.

Gordon

 

[tourgis2000]

Thank you so much Alan, your solution is just what I was looking for. The only thing I need to do is fiddle with the user input so that I can identify where the last time band starts because there can be up to ten entries. I would have been banging my head against the wall for days without your help!

And thank you Gordon as well. Your solution doesn't seem to identify the BAND_ID for each time, which I need, and I believe that an array may take more processing (I might have up to 200,000 rows of data to analyse) although I am happy to be convinced otherwise.

Martin

 

[SydneyGeek]

Hi Martin,

You can extend Alan's solution by creating a pivot table. Date in the Row, Band in the Column, Value in the Data. That will give you a band-by-band breakdown for each date in the data set.

Denis

 

[gsbelbin]

Sorry, I must have misunderstood what you were after. I thought you wanted to look for which times were in each band and then get the total for each band. So for example (forgetting about the 30hr clock issue to avoid confusion) since the 4th, 6th and 7th times are in band 4 you would add the values for these times and come up with 15+15+16=46.

Gordon

 

[tourgis2000]

Hi Denis,

Yes indeed - and that happens at a later stage when all the data is analysed. Although actually I am trying to use SUMIFs to analyse the data (with dynamic range names) to save time.

Thanks,

Martin

 

[SydneyGeek]

You can use a dynamic range name with a pivot table too. Could cut down on the SUMIF formulas... all you have to do is refresh the pivot

Denis

 

[tourgis2000]

Sorry, I must have misunderstood what you were after. I thought you wanted to look for which times were in each band and then get the total for each band. So for example (forgetting about the 30hr clock issue to avoid confusion) since the 4th, 6th and 7th times are in band 4 you would add the values for these times and come up with 15+15+16=46.

Gordon

Hi Gordon,

You are quite correct, that is exactly what I need, but the bands are dynamic. I need to make multiple calculations on each row of data (the time is just one field) and from those further calculations are made and so on. Really it is probably better to use Access in some respects but I have enough problems with Excel as you can tell!

Martin

 

[tourgis2000]

You can use a dynamic range name with a pivot table too. Could cut down on the SUMIF formulas... all you have to do is refresh the pivot

Denis

Hi Denis,

I didn't know that so thanks for the info. But I'm concerned about the size of the file. Even though I convert the first stage calculations to values for the sheet that does the analysis, it is still likely to be a very big file. I have noticed how much pivot tables increase file size (the cache I believe) and in order to cut down on repetitive tasks I am trying to find a balance between using pivots and calculations.

Martin