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

=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

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

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

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

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