Excel newbie - Totals for 15 minute intervals

**shaddowxxx** · 05-20-2013, 10:24 AM

Hi all,

This might be in the wrong sub-forum but here goes. I have some data that i need to display in a graph which is basically a data count total for every 15 minutes if that makes sense,

A B
07:00 189
07:05 189
07:10 189
07:10 50
07:10 200
07:15 150
07:30 150
07:40 160
07:40 18
07:50 90
07:50 78
08:15 78
08:30 159
08:35 189
08:40 189
08:40 200
08:55 150

Column A represents the time and B is a count, what i would like is something that can automatically pick up the full count for a 15 minute window between 07:00-07:14, 07:15:07:29, 07:30-07:44 and so on and so on up until around 23:59. I would then like to represent this in a graph. I know i can manually just sum up the data i have but what if every day it was different times in the 15 minute period, is there a way i can drop in data and it would auto pick up whether it was 10 entries in the 15min period or 2 entries without me manually adjusting anything. Lol it seems clear in my mind but i'll probably be way off

I am a newbie in this area and would greatly appreciate any assistance

Thanks

**MarvinP** · 05-20-2013, 11:40 AM

Hi shaddowxxx and welcome to the forum.

There might be easier ways to do this problem but here is my thinking. Create a table of every 15 minutes in a day and assign a group to them. Then do a VLookup using the TRUE so it will put things into 15 minute group. Then Do a Pivot Table using the groups. See the attached for my test.

**ragulduy** · 05-20-2013, 11:58 AM

I'd think you could do this with sumif? something like
=SUMIF(A:A,">=07:00",B:B)-SUMIF(A:A,">=07:30",B:B) and so on for the different times.

**shaddowxxx** · 05-20-2013, 04:12 PM

Thanks for both replies and effort, i think Yudlugar's method seems to be the one that will help most. Thanks you both so much

**shaddowxxx** · 05-21-2013, 08:46 AM

Aaarggh I'm back again

Ok using the same example, what i would like to do now is this.

In column B there is a number value however I would like that looked up from elsewhere. The data I may have is like this so WWW = 100 XXX = 50, YYY = 100 ZZZ = 100 etc etc so when I fill in the columns A & B i would input 07:00 for A1 and input WWW for B1 but it would display the value of 100 in B1 instead.

Again it seems plausible in my head but im not sure if its do-able

Thanks again for all the help