| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
| Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
| Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
| how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions | |||
Linear Mode
