If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|