Summing up the data at regular time intervals

[Miramolin]

Hello,
I have a data set that is now in two columns.
One column denotes time.
The other column denotes a cost variable.


An example of 2 rows:


Time..............................Cost
Sept 8 2014 14:23:03....3.45
Sept 8 2014 14:04:45....6.01


…and so on (It literally looks like that, please ignore the dots)


What I want is for cost to be summated at regular time intervals, whether daily, weekly or even hourly, (I need this for time series analysis (but don’t worry about that)). The number of costs varies per hour and per day so I imagine the way forward is a formula that can 'read' the info in the time column.


Guys, what is the most simple way of doing this?

Oh, and I'm using Excel for Mac, and I'm pretty new to the idea of macros!


- Thanks in advance ! : )
M

 

[cyrilbrd]

Hi, how will you set said interval? will it be stated in a specific cell?

 

[Miramolin]

Thanks for the response cyrilbird.

I suppose I just need a function that will, for instance:

add up all the cost values which have associated time values between 14:00:00 and 15:00:00 and output the sum, presumably next to a column that states 15:00:00. The function should do the same for 15:00:00 to 16:00:00 and so on and so forth.

I expect that my understanding of excel is quite basic compared to yours, so I am unfamiliar with how to 'set an interval'. But I think I know what you mean.

I look forward to your response!
M

 

[runwest]

	A	B
1	9/18/14 2:00 PM
2	9/18/14 3:00 PM	=SUMIFS(*cost col*<cost col="">, *time col*<time col="">, ">=" & A1, *time col*, "<" & A2)</time></cost>
3	9/18/14 4:00 PM	=SUMIFS(*cost col*<cost col="">, *time col*<time col="">, ">=" & A2, *time col*, "<" & A3)</time></cost>

<tbody>
</tbody>

If you're setting up the time intervals in such a way they are listed in a column this will work. The SUMIFS formula will find all times greater than or equal to the previous time and less than the current time and add their related cost. For example, B2 is finding all times between 9/18/14 2:00 PM and 9/18/14 3:00 PM and adding their related costs.

 

[Miramolin]

Thanks for the formula runwest,

I think the problem is that each cost value is recorded at a specific time, which makes the challenge more tricky!

For instance here is a list of the first few 'time' values

Sept 8 2014 14:52:03

Sept 8 2014 14:47:40

Sept 8 2014 14:39:19

Sept 8 2014 14:35:33

Sept 8 2014 14:33:57

Sept 8 2014 14:22:11

Sept 8 2014 14:21:54

Sept 8 2014 14:04:37

Sept 8 2014 13:33:58

Sept 8 2014 12:56:12

<tbody>
</tbody>

What is required is a column that states between 13:00:00 and 14:00:00 the total cost was 'x'. and that needs to happen for ± 30 days, (where each time has a corresponding cost)

So I presume I need a formula that can 'read the time'... : S

 

[cyrilbrd]

Thanks for the formula runwest,

I think the problem is that each cost value is recorded at a specific time, which makes the challenge more tricky!

For instance here is a list of the first few 'time' values

Sept 8 2014 14:52:03

Sept 8 2014 14:47:40

Sept 8 2014 14:39:19

Sept 8 2014 14:35:33

Sept 8 2014 14:33:57

Sept 8 2014 14:22:11

Sept 8 2014 14:21:54

Sept 8 2014 14:04:37

Sept 8 2014 13:33:58

Sept 8 2014 12:56:12

<tbody>
</tbody>

What is required is a column that states between 13:00:00 and 14:00:00 the total cost was 'x'. and that needs to happen for ± 30 days, (where each time has a corresponding cost)

So I presume I need a formula that can 'read the time'... : S

Given in A1:

timecode	val		starts	13:00
Sep 09, 2014 12:40	8		ends	14:00
Sep 09, 2014 12:55	9
Sep 09, 2014 13:11	4		Sum:	12
Sep 09, 2014 13:27	1
Sep 09, 2014 13:43	6
Sep 09, 2014 13:59	1
Sep 09, 2014 14:15	4
Sep 09, 2014 14:30	8

<tbody>
</tbody>

Formula in E4 is =SUMPRODUCT(--(MOD(A2:A9,1)>=E1),--(MOD(A2:A9,1)<=E2),B2:B9) adjust range to fit your query.
Note:
cells in A2 and below are dates (9/9/2014 12:40:00 PM) formatted as mmm dd, yyyy h:mm:ss
and cells in E1 and E2 are time (1:00:00 PM) formatted as h:mm