Average

  • Thread starter Thread starter Jeff
  • Start date Start date
J

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.
 
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.
 
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.
 
Thanks again for your help on this. However it did not work for the Month. I
did get the answer from another user.
 
Thanks so much!

T. Valko said:
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.
Click to expand...
 
Back
Top