Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to convert half hourly data into hourly
Hi,
I have a load of stats reported in half hourly intervals that I need to transfer into hourly information- sounds easy but I'm struggling! Data is in the following format: Time span # Calls Time span # Calls 00:00-00:30 5 00:00- 01:00 ? 00:31- 01:00 10 01:01- 02:00 ? 01:01- 01:30 2 02:01- 03:00 ? 01:31-02:00 22 03:01-04:00 ? 02:01-02:30 44 04:01-05:00 ? 02:31-03:00 1 03:01-03:30 15 03:31-04:00 12 04:01-04:30 62 04:31-05:00 7 Basically I need to populate the cells on the right marked with a ? with the sum of the corresponding half hourly cells on the left- I have 12 months data to work through.... any ideas?? Cheers |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to convert half hourly data into hourly
=SUMPRODUCT(--(LEFT($A$2:$A$500,5)=LEFT(D2,5))+(RIGHT($A$2:$A$50 0,5)=RIGHT(D2,5)),$B$2:$B$500)
"kippers" wrote: Hi, I have a load of stats reported in half hourly intervals that I need to transfer into hourly information- sounds easy but I'm struggling! Data is in the following format: Time span # Calls Time span # Calls 00:00-00:30 5 00:00- 01:00 ? 00:31- 01:00 10 01:01- 02:00 ? 01:01- 01:30 2 02:01- 03:00 ? 01:31-02:00 22 03:01-04:00 ? 02:01-02:30 44 04:01-05:00 ? 02:31-03:00 1 03:01-03:30 15 03:31-04:00 12 04:01-04:30 62 04:31-05:00 7 Basically I need to populate the cells on the right marked with a ? with the sum of the corresponding half hourly cells on the left- I have 12 months data to work through.... any ideas?? Cheers |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to convert half hourly data into hourly
In A1 thru B10:
00:00-00:30 5 00:31-01:00 10 01:01-01:30 2 01:31-02:00 22 02:01-02:30 44 02:31-03:00 1 03:01-03:30 15 03:31-04:00 12 04:01-04:30 62 04:31-05:00 7 then in C1 thru C10: nothing =B2+B1 nothing =B4+B3 nothing =B6+B5 nothing =B8+B7 nothing =B10+B9 -- Gary's Student gsnu200702 "Teethless mama" wrote: =SUMPRODUCT(--(LEFT($A$2:$A$500,5)=LEFT(D2,5))+(RIGHT($A$2:$A$50 0,5)=RIGHT(D2,5)),$B$2:$B$500) "kippers" wrote: Hi, I have a load of stats reported in half hourly intervals that I need to transfer into hourly information- sounds easy but I'm struggling! Data is in the following format: Time span # Calls Time span # Calls 00:00-00:30 5 00:00- 01:00 ? 00:31- 01:00 10 01:01- 02:00 ? 01:01- 01:30 2 02:01- 03:00 ? 01:31-02:00 22 03:01-04:00 ? 02:01-02:30 44 04:01-05:00 ? 02:31-03:00 1 03:01-03:30 15 03:31-04:00 12 04:01-04:30 62 04:31-05:00 7 Basically I need to populate the cells on the right marked with a ? with the sum of the corresponding half hourly cells on the left- I have 12 months data to work through.... any ideas?? Cheers |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to convert half hourly data into hourly
Try this:
=SUM(OFFSET(B$2,(ROWS($1:1)-1)*2,,2)) Copy down as needed. Biff "kippers" wrote in message ... Hi, I have a load of stats reported in half hourly intervals that I need to transfer into hourly information- sounds easy but I'm struggling! Data is in the following format: Time span # Calls Time span # Calls 00:00-00:30 5 00:00- 01:00 ? 00:31- 01:00 10 01:01- 02:00 ? 01:01- 01:30 2 02:01- 03:00 ? 01:31-02:00 22 03:01-04:00 ? 02:01-02:30 44 04:01-05:00 ? 02:31-03:00 1 03:01-03:30 15 03:31-04:00 12 04:01-04:30 62 04:31-05:00 7 Basically I need to populate the cells on the right marked with a ? with the sum of the corresponding half hourly cells on the left- I have 12 months data to work through.... any ideas?? Cheers |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to convert half hourly data into hourly
Spot on- cheers guys
"T. Valko" wrote: Try this: =SUM(OFFSET(B$2,(ROWS($1:1)-1)*2,,2)) Copy down as needed. Biff "kippers" wrote in message ... Hi, I have a load of stats reported in half hourly intervals that I need to transfer into hourly information- sounds easy but I'm struggling! Data is in the following format: Time span # Calls Time span # Calls 00:00-00:30 5 00:00- 01:00 ? 00:31- 01:00 10 01:01- 02:00 ? 01:01- 01:30 2 02:01- 03:00 ? 01:31-02:00 22 03:01-04:00 ? 02:01-02:30 44 04:01-05:00 ? 02:31-03:00 1 03:01-03:30 15 03:31-04:00 12 04:01-04:30 62 04:31-05:00 7 Basically I need to populate the cells on the right marked with a ? with the sum of the corresponding half hourly cells on the left- I have 12 months data to work through.... any ideas?? Cheers |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to convert half hourly data into hourly
All,
Whilst converting the half hourly data to hourly, I now have a column entitled 'Longest Call'. rather than taking the sum of the two half hourly cells and placing it in an hourly column, I now need to analyse two half hourly cells and select the greatest value and place this in teh hourly cell i.e. Half Hourly Time Longest Call Hourly Time Longest Call 00:00-00:30 01:00:34 00:00-01:00 ? 00:31-01:00 00:45:53 01:01-02:00 ? 01:01-01:30 00:00:56 02:01-03:00 ? 01:31-02:00 00:00:56 03:01-04:00 ? Cheers, "kippers" wrote: Spot on- cheers guys "T. Valko" wrote: Try this: =SUM(OFFSET(B$2,(ROWS($1:1)-1)*2,,2)) Copy down as needed. Biff "kippers" wrote in message ... Hi, I have a load of stats reported in half hourly intervals that I need to transfer into hourly information- sounds easy but I'm struggling! Data is in the following format: Time span # Calls Time span # Calls 00:00-00:30 5 00:00- 01:00 ? 00:31- 01:00 10 01:01- 02:00 ? 01:01- 01:30 2 02:01- 03:00 ? 01:31-02:00 22 03:01-04:00 ? 02:01-02:30 44 04:01-05:00 ? 02:31-03:00 1 03:01-03:30 15 03:31-04:00 12 04:01-04:30 62 04:31-05:00 7 Basically I need to populate the cells on the right marked with a ? with the sum of the corresponding half hourly cells on the left- I have 12 months data to work through.... any ideas?? Cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual control of link updating for downloaded quotes? | Excel Discussion (Misc queries) | |||
Convert data into standard military time format | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Excel 2003, Convert EXISTING Worksheet Data to XML? | Excel Discussion (Misc queries) |