Prev: Symbol = Character Code 25BA
Next: Making a comment using a cell?
From: Ryan Hicks on 24 May 2010 15:42
I need help coming up will a function that will average a range in one column
based on if the hours match. Here is my set up.

-------A-------B
1.....09:21....12
2.....09:46....24
3.....09:57....17
4.....10:04....22

I need to average column B if column A's hour is the same as the hour in a
cell in another worksheet.

From: Luke M on 24 May 2010 16:14
This is an array* function
=AVERAGE(IF(HOUR(A2:A10)=HOUR(C3),B2:B10))

*Array functions must be confirmed using Ctrl+Shift+Enter, not just Enter.

--
Best Regards,

Luke M
"Ryan Hicks" <RyanHicks(a)discussions.microsoft.com> wrote in message
news:AFA221E3-F00F-4056-975A-06D121FC3E95(a)microsoft.com...
>I need help coming up will a function that will average a range in one
>column
> based on if the hours match. Here is my set up.
>
> -------A-------B
> 1.....09:21....12
> 2.....09:46....24
> 3.....09:57....17
> 4.....10:04....22
>
> I need to average column B if column A's hour is the same as the hour in a
> cell in another worksheet.
>


From: Fred Smith on 24 May 2010 16:24
=SUMPRODUCT(B1:B4,--(HOUR(A1:A4)=Sheet2!A1))/SUMPRODUCT(--(HOUR(A1:A4)=Sheet2!A1))
Adjust the range for the "cell in another worksheet" to suit.

Regards,
Fred

"Ryan Hicks" <RyanHicks(a)discussions.microsoft.com> wrote in message
news:AFA221E3-F00F-4056-975A-06D121FC3E95(a)microsoft.com...
>I need help coming up will a function that will average a range in one
>column
> based on if the hours match. Here is my set up.
>
> -------A-------B
> 1.....09:21....12
> 2.....09:46....24
> 3.....09:57....17
> 4.....10:04....22
>
> I need to average column B if column A's hour is the same as the hour in a
> cell in another worksheet.
>

 | 
Pages: 1
Prev: Symbol = Character Code 25BA
Next: Making a comment using a cell?