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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,085
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,085
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,826
Messages
5,855,875
Members
431,771
Latest member
CoryMelth

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
Top