Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 51
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,718
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 51
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 51
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Manual control of link updating for downloaded quotes? dk_ Excel Discussion (Misc queries) 9 November 15th 06 02:04 PM
Convert data into standard military time format geog Excel Discussion (Misc queries) 2 December 12th 05 08:46 PM
Excel Macro to Copy & Paste [email protected] Excel Worksheet Functions 0 December 1st 05 02:56 PM
From several workbooks onto one excel worksheet steve Excel Discussion (Misc queries) 6 December 1st 05 09:03 AM
Excel 2003, Convert EXISTING Worksheet Data to XML? [email protected] Excel Discussion (Misc queries) 4 November 16th 05 05:45 AM


All times are GMT +1. The time now is 09:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"