How to convert half hourly data into hourly

[kippers]

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

 

[Teethless mama]

=SUMPRODUCT(--(LEFT($A$2:$A$500,5)=LEFT(D2,5))+(RIGHT($A$2:$A$500,5)=RIGHT(D2,5)),$B$2:$B$500)

 

[Gary''s Student]

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

 

[T. Valko]

Try this:

=SUM(OFFSET(B$2,(ROWS($1:1)-1)*2,,2))

Copy down as needed.

Biff

 

[kippers]

Spot on- cheers guys

Try this:

=SUM(OFFSET(B$2,(ROWS($1:1)-1)*2,,2))

Copy down as needed.

Biff

 

[kippers]

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,