Prev: Need a little math help in making a formula
Next: Index Match Inconsistencies
From: Chris26 on 2 Jun 2010 09:29
Hi
I have the following data
Col-A Col-B
1/10/2009 10:20 0.006
1/10/2009 10:22 0.007
1/10/2009 10:24 0.006
........
1/06/2010 15:00 0.015

etc
What I would like to be able to do, is to calculate the average of the
vaules in Col B only between certain time periods i.e. 2am and 4am for each
day in the data set

Any help appreciated.
Many Thanks
Chris
From: Don Guillett on 2 Jun 2010 09:46
=AVERAGE(IF((HOUR(F8:F28)>=10)*(HOUR(F8:F28)<11),G8:G28))
An array formula that must be entered using ctrl+shift+enter
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Chris26" <Chris26(a)discussions.microsoft.com> wrote in message
news:9C90C6AF-1577-4C89-B370-87DDB475A9BD(a)microsoft.com...
> Hi
> I have the following data
> Col-A Col-B
> 1/10/2009 10:20 0.006
> 1/10/2009 10:22 0.007
> 1/10/2009 10:24 0.006
> .......
> 1/06/2010 15:00 0.015
>
> etc
> What I would like to be able to do, is to calculate the average of the
> vaules in Col B only between certain time periods i.e. 2am and 4am for
> each
> day in the data set
>
> Any help appreciated.
> Many Thanks
> Chris

From: Jacob Skaria on 3 Jun 2010 07:26
With start datetime in cell C1 and enddate time in cell D1 try the below
array formula. Press CTRL+SHIFT+ENTER to enter the formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula>}"

=AVERAGE(IF((A1:A10>=C1)*(A1:A10<=D1),B1:B10))

--
Jacob (MVP - Excel)


"Chris26" wrote:

> Hi
> I have the following data
> Col-A Col-B
> 1/10/2009 10:20 0.006
> 1/10/2009 10:22 0.007
> 1/10/2009 10:24 0.006
> .......
> 1/06/2010 15:00 0.015
>
> etc
> What I would like to be able to do, is to calculate the average of the
> vaules in Col B only between certain time periods i.e. 2am and 4am for each
> day in the data set
>
> Any help appreciated.
> Many Thanks
> Chris
 | 
Pages: 1
Prev: Need a little math help in making a formula
Next: Index Match Inconsistencies