Average

[Jeff]

Hi all,

I need to calculate the average of b3:b9 if the month in a3:a9 is equal to
the month in a1, can anyone help me? The answer is 180 but I don't know how
to write the formula.

A B
2/14/2009
Date 8
2/1/2009 100.00%
1/31/2009 100.00%
2/1/2009 100.00%
2/1/2009 100.00%
2/1/2009 100.00%
2/1/2009
2/1/2009 500.00%

Thanks so much in advance.

 

[Shane Devenshire]

Hi,

In 2007

=AVERAGEIF(B3:B9,A3:A9,A1)

In 2003

=AVERAGE(IF(A3:A9=A1,B3:B9,""))

This second formula is an array and must be entered by pressing
Shift+Ctrl+Enter.

 

[T. Valko]

Try one of these array formulas** :

=AVERAGE(IF(MONTH(A3:A9)=MONTH(A1),B3:B9))

Based on your data the result is 150%. It's including the empty cell that
corresponds to 2/1/2009 and evaluating it as 0. If you want to exclude the
empty cell:

=AVERAGE(IF((MONTH(A3:A9)=MONTH(A1))*(B3:B9<>""),B3:B9))

This one returns 180%

Format as Percentage

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Jeff]

Thanks again for your help on this. However it did not work for the Month. I
did get the answer from another user.

 

[Jeff]

Thanks so much!

Try one of these array formulas** :

=AVERAGE(IF(MONTH(A3:A9)=MONTH(A1),B3:B9))

Based on your data the result is 150%. It's including the empty cell that
corresponds to 2/1/2009 and evaluating it as 0. If you want to exclude the
empty cell:

=AVERAGE(IF((MONTH(A3:A9)=MONTH(A1))*(B3:B9<>""),B3:B9))

This one returns 180%

Format as Percentage

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks so much!