Average
 

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.

Average
 

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.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Jeff" wrote:

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.

Average
 

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.

Average
 

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

"Shane Devenshire" wrote:

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.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Jeff" wrote:

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.

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.

Average
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Jeff" wrote in message
...
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.