How to calculate totals from a list

**jamie007** · 09-06-2012, 05:33 AM

I hope you can help with my problem

I have a list of dates in Column A and a list of times in Column B set at 15 minute intervals. In Column C I have a list of values which vary from 0 - 50 and I have several of these spreadsheets.

My Problem is to find from Column C the maximum sequential (i.e. one after the other with no gaps) 4 hour event (i.e. 16 values).

For example:

06/07/2012 03:30:00 0.2
06/07/2012 03:45:00 .
06/07/2012 04:00:00 0.2
06/07/2012 04:15:00 0.2
06/07/2012 04:30:00 0.4
06/07/2012 04:45:00 0.4
06/07/2012 05:00:00 .
06/07/2012 05:15:00 .
06/07/2012 05:30:00 .
06/07/2012 05:45:00 0.2
06/07/2012 06:00:00 0.2
06/07/2012 06:15:00 .
06/07/2012 06:30:00 .
06/07/2012 06:45:00 0.2
06/07/2012 07:00:00 0.2
06/07/2012 07:15:00 0.2
06/07/2012 07:30:00 0.6
06/07/2012 07:45:00 0.6
06/07/2012 08:00:00 0.4
06/07/2012 08:15:00 1
06/07/2012 08:30:00 0.6
06/07/2012 08:45:00 2
06/07/2012 09:00:00 1.2
06/07/2012 09:15:00 1.4
06/07/2012 09:30:00 1.4
06/07/2012 09:45:00 1
06/07/2012 10:00:00 0.8
06/07/2012 10:15:00 0.4
06/07/2012 10:30:00 0.4
06/07/2012 10:45:00 0.4
06/07/2012 11:00:00 0.6
06/07/2012 11:15:00 1
06/07/2012 11:30:00 1
06/07/2012 11:45:00 0.8
06/07/2012 12:00:00 0.6
06/07/2012 12:15:00 0.4
06/07/2012 12:30:00 0.4
06/07/2012 12:45:00 1
06/07/2012 13:00:00 0.2
06/07/2012 13:15:00 0.6
06/07/2012 13:30:00 1
06/07/2012 13:45:00 0.4
06/07/2012 14:00:00 1.2
06/07/2012 14:15:00 0.8
06/07/2012 14:30:00 0.4
06/07/2012 14:45:00 0.6
06/07/2012 15:00:00 0.6
06/07/2012 15:15:00 0.4
06/07/2012 15:30:00 0.6
06/07/2012 15:45:00 0.4
06/07/2012 16:00:00 .
06/07/2012 16:15:00 .
06/07/2012 16:30:00 .
06/07/2012 16:45:00 .
06/07/2012 17:00:00 0.2

Thanks in advance

**jamie007** · 09-06-2012, 09:49 AM

Can anyone help?

Essentially it is the maximum of the sum of 16 consecutive values in column c that I need (i.e max of sum of c1-c16,c2-c17,etc)

**duanzhuanming** · 09-06-2012, 12:16 PM

Salut,
I think you can use marco for this problem.if you want to use function. you should pass through intermediate calculation:
see attached file below.

**tigeravatar** · 09-06-2012, 12:46 PM

jamie007,

Here is an alternate method that doesn't use VBA. In cell F2 is this formula to get the max of summing 16 consecutive values from column C:

=MAX(INDEX(SUBTOTAL(9,OFFSET(C1:C16,ROW(INDIRECT("1:"&ROWS(C1:C55)-15))-1,)),))

See attached.

**jamie007** · 09-07-2012, 01:37 PM

Thank you duanz and tigeravatar; both these approaches work just fine

Very Clever. SOLVED.

**Cutter** · 09-08-2012, 08:44 AM

@ jamie007

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as an infrequent user of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.