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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi, how will you set said interval? will it be stated in a specific cell?
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,214,324
Messages
6,118,890
Members
448,854
Latest member
Eduard_Stoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top