Average
Thanks so much!
"T. Valko" wrote:
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<""),B 3: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.
--
Biff
Microsoft Excel MVP
"Jeff" wrote in message
...
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.
|