View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Bernard Liengme[_3_] Bernard Liengme[_3_] is offline
external usenet poster
 
Posts: 1,104
Default Averaging an interval of data

I put times like yours in A1:A18 and numbers in B1:B18
In J1:J4 I enters 1,2,3,4
In K1 I used the formula
=SUMPRODUCT(--(HOUR($A$1:$A$18-TIME(0,15,0))=J1-1),$B$1:$B$18)/4
This gave me the average of the first four numbers with times up to 1:00
inclusive
When I copied it down to K2, I get the average of the numbers for 1:15 up to
2:00 inclusive
and so on
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"KK" wrote in message
...
Each hour is broken into 15 minute intervals. I'd like to average four
intervals in each hour. How do I do that?

There are many intervals so I need a quick solution, can't do it
manually.

Here's a screenshot of example data:

http://www.aww-kittah-aww.com/up/public/73735/xls.jpg