Summing up the data at regular time intervals

Miramolin

New Member
Joined
Sep 16, 2014
Messages
5
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi, how will you set said interval? will it be stated in a specific cell?
 

Miramolin

New Member
Joined
Sep 16, 2014
Messages
5
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

New Member
Joined
Aug 6, 2013
Messages
9
AB
19/18/14 2:00 PM
29/18/14 3:00 PM=SUMIFS(*cost col*<cost col="">, *time col*<time col="">, ">=" & A1, *time col*, "<" & A2)</time></cost>
39/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.
 
Last edited:

Miramolin

New Member
Joined
Sep 16, 2014
Messages
5
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

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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:
timecodevalstarts13:00
Sep 09, 2014 12:408ends14:00
Sep 09, 2014 12:559
Sep 09, 2014 13:114Sum:12
Sep 09, 2014 13:271
Sep 09, 2014 13:436
Sep 09, 2014 13:591
Sep 09, 2014 14:154
Sep 09, 2014 14:308

<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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,809
Messages
5,525,002
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top