| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Jululian
hi all
i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
Hi George
Could you please elaborate your query..preferably with an example... -- Jacob (MVP - Excel) "George A. Jululian" wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
Try this array* formula:
=AVERAGE(IF(MONTH(A1:A100)=1,B1:B100)) where the 1 is for January. Adjust the ranges to suit your data. * An array formula must be committed using the key combination of Ctrl- Shift-Enter (CSE) rather than the usual <enter. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. If you need to edit/amend the formula you must use CSE again. If you want to copy the formula down and have it automatically adjust for different months of the year, then you can do this: =AVERAGE(IF(MONTH(A$1:A$100)=ROW(A1),B$1:B$100)) Again, this is an array formula. Hope this helps. Pete On Apr 27, 10:17*am, George A. Jululian wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A * * * * * * * * * * * * * * *B 07/03/2006 * * *0.913 *i need average rate for each month if its January the average rate is so |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
Hi,
Colunm A ColunmB 01/17/2006 0.8631 01/18/2006 0.8618 02/19/2006 0.8625 02/20/2006 0.8613 03/21/2006 0.8582 04/22/2006 0.8661 i need formula to find average rate for colunm b for each month in colunm A regrads "Jacob Skaria" wrote: Hi George Could you please elaborate your query..preferably with an example... -- Jacob (MVP - Excel) "George A. Jululian" wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =AVERAGE(IF(TEXT(A1:A10,"mmmyyyy")="Jan2006",B1:B1 0)) -- Jacob (MVP - Excel) "George A. Jululian" wrote: Hi, Colunm A ColunmB 01/17/2006 0.8631 01/18/2006 0.8618 02/19/2006 0.8625 02/20/2006 0.8613 03/21/2006 0.8582 04/22/2006 0.8661 i need formula to find average rate for colunm b for each month in colunm A regrads "Jacob Skaria" wrote: Hi George Could you please elaborate your query..preferably with an example... -- Jacob (MVP - Excel) "George A. Jululian" wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
FIRST MANY THANKS
i tried and adjust the formula (array) it retruned #value! Regards "Pete_UK" wrote: Try this array* formula: =AVERAGE(IF(MONTH(A1:A100)=1,B1:B100)) where the 1 is for January. Adjust the ranges to suit your data. * An array formula must be committed using the key combination of Ctrl- Shift-Enter (CSE) rather than the usual <enter. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. If you need to edit/amend the formula you must use CSE again. If you want to copy the formula down and have it automatically adjust for different months of the year, then you can do this: =AVERAGE(IF(MONTH(A$1:A$100)=ROW(A1),B$1:B$100)) Again, this is an array formula. Hope this helps. Pete On Apr 27, 10:17 am, George A. Jululian wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
Many thanks
the result should read 0.860926 not 0.893833 please advice regards "Jacob Skaria" wrote: Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =AVERAGE(IF(TEXT(A1:A10,"mmmyyyy")="Jan2006",B1:B1 0)) -- Jacob (MVP - Excel) "George A. Jululian" wrote: Hi, Colunm A ColunmB 01/17/2006 0.8631 01/18/2006 0.8618 02/19/2006 0.8625 02/20/2006 0.8613 03/21/2006 0.8582 04/22/2006 0.8661 i need formula to find average rate for colunm b for each month in colunm A regrads "Jacob Skaria" wrote: Hi George Could you please elaborate your query..preferably with an example... -- Jacob (MVP - Excel) "George A. Jululian" wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
The formula I posted returns the average for Jan2006 which is .86245 ..I only
find 2 entries for Jan2006....Am I missing something..... -- Jacob (MVP - Excel) "George A. Jululian" wrote: Many thanks the result should read 0.860926 not 0.893833 please advice regards "Jacob Skaria" wrote: Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =AVERAGE(IF(TEXT(A1:A10,"mmmyyyy")="Jan2006",B1:B1 0)) -- Jacob (MVP - Excel) "George A. Jululian" wrote: Hi, Colunm A ColunmB 01/17/2006 0.8631 01/18/2006 0.8618 02/19/2006 0.8625 02/20/2006 0.8613 03/21/2006 0.8582 04/22/2006 0.8661 i need formula to find average rate for colunm b for each month in colunm A regrads "Jacob Skaria" wrote: Hi George Could you please elaborate your query..preferably with an example... -- Jacob (MVP - Excel) "George A. Jululian" wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
i sort the table
01/01/2006 0.8436 01/02/2006 0.8443 01/03/2006 0.8437 01/04/2006 0.8431 01/05/2006 0.846 01/06/2006 0.8618 01/07/2006 0.8603 01/08/2006 0.8635 01/09/2006 0.8635 01/10/2006 0.8625 01/11/2006 0.8602 01/12/2006 0.8606 01/13/2006 0.8603 01/14/2006 0.8565 01/15/2006 0.8667 01/16/2006 0.8668 01/17/2006 0.8631 01/18/2006 0.8618 01/19/2006 0.8625 01/20/2006 0.8613 01/21/2006 0.8582 01/22/2006 0.8661 01/23/2006 0.8661 01/24/2006 0.8707 01/25/2006 0.8746 01/26/2006 0.8738 01/27/2006 0.872 01/28/2006 0.8682 01/29/2006 0.863 01/30/2006 0.863 01/31/2006 0.8609 Regards "Jacob Skaria" wrote: The formula I posted returns the average for Jan2006 which is .86245 ..I only find 2 entries for Jan2006....Am I missing something..... -- Jacob (MVP - Excel) "George A. Jululian" wrote: Many thanks the result should read 0.860926 not 0.893833 please advice regards "Jacob Skaria" wrote: Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =AVERAGE(IF(TEXT(A1:A10,"mmmyyyy")="Jan2006",B1:B1 0)) -- Jacob (MVP - Excel) "George A. Jululian" wrote: Hi, Colunm A ColunmB 01/17/2006 0.8631 01/18/2006 0.8618 02/19/2006 0.8625 02/20/2006 0.8613 03/21/2006 0.8582 04/22/2006 0.8661 i need formula to find average rate for colunm b for each month in colunm A regrads "Jacob Skaria" wrote: Hi George Could you please elaborate your query..preferably with an example... -- Jacob (MVP - Excel) "George A. Jululian" wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
I am getting 0.860925806....Please check the formula range and try again...
-- Jacob (MVP - Excel) "George A. Jululian" wrote: i sort the table 01/01/2006 0.8436 01/02/2006 0.8443 01/03/2006 0.8437 01/04/2006 0.8431 01/05/2006 0.846 01/06/2006 0.8618 01/07/2006 0.8603 01/08/2006 0.8635 01/09/2006 0.8635 01/10/2006 0.8625 01/11/2006 0.8602 01/12/2006 0.8606 01/13/2006 0.8603 01/14/2006 0.8565 01/15/2006 0.8667 01/16/2006 0.8668 01/17/2006 0.8631 01/18/2006 0.8618 01/19/2006 0.8625 01/20/2006 0.8613 01/21/2006 0.8582 01/22/2006 0.8661 01/23/2006 0.8661 01/24/2006 0.8707 01/25/2006 0.8746 01/26/2006 0.8738 01/27/2006 0.872 01/28/2006 0.8682 01/29/2006 0.863 01/30/2006 0.863 01/31/2006 0.8609 Regards "Jacob Skaria" wrote: The formula I posted returns the average for Jan2006 which is .86245 ..I only find 2 entries for Jan2006....Am I missing something..... -- Jacob (MVP - Excel) "George A. Jululian" wrote: Many thanks the result should read 0.860926 not 0.893833 please advice regards "Jacob Skaria" wrote: Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =AVERAGE(IF(TEXT(A1:A10,"mmmyyyy")="Jan2006",B1:B1 0)) -- Jacob (MVP - Excel) "George A. Jululian" wrote: Hi, Colunm A ColunmB 01/17/2006 0.8631 01/18/2006 0.8618 02/19/2006 0.8625 02/20/2006 0.8613 03/21/2006 0.8582 04/22/2006 0.8661 i need formula to find average rate for colunm b for each month in colunm A regrads "Jacob Skaria" wrote: Hi George Could you please elaborate your query..preferably with an example... -- Jacob (MVP - Excel) "George A. Jululian" wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
Do you have text in A1:A100?
=month() will break if you do. George A. Jululian wrote: FIRST MANY THANKS i tried and adjust the formula (array) it retruned #value! Regards "Pete_UK" wrote: Try this array* formula: =AVERAGE(IF(MONTH(A1:A100)=1,B1:B100)) where the 1 is for January. Adjust the ranges to suit your data. * An array formula must be committed using the key combination of Ctrl- Shift-Enter (CSE) rather than the usual <enter. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. If you need to edit/amend the formula you must use CSE again. If you want to copy the formula down and have it automatically adjust for different months of the year, then you can do this: =AVERAGE(IF(MONTH(A$1:A$100)=ROW(A1),B$1:B$100)) Again, this is an array formula. Hope this helps. Pete On Apr 27, 10:17 am, George A. Jululian wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so . -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
Sir
if i sort the table and made the only for 31 day the answar 0.860925806 which is tture but the table 365 days and mixed date and month regards "Jacob Skaria" wrote: I am getting 0.860925806....Please check the formula range and try again... -- Jacob (MVP - Excel) "George A. Jululian" wrote: i sort the table 01/01/2006 0.8436 01/02/2006 0.8443 01/03/2006 0.8437 01/04/2006 0.8431 01/05/2006 0.846 01/06/2006 0.8618 01/07/2006 0.8603 01/08/2006 0.8635 01/09/2006 0.8635 01/10/2006 0.8625 01/11/2006 0.8602 01/12/2006 0.8606 01/13/2006 0.8603 01/14/2006 0.8565 01/15/2006 0.8667 01/16/2006 0.8668 01/17/2006 0.8631 01/18/2006 0.8618 01/19/2006 0.8625 01/20/2006 0.8613 01/21/2006 0.8582 01/22/2006 0.8661 01/23/2006 0.8661 01/24/2006 0.8707 01/25/2006 0.8746 01/26/2006 0.8738 01/27/2006 0.872 01/28/2006 0.8682 01/29/2006 0.863 01/30/2006 0.863 01/31/2006 0.8609 Regards "Jacob Skaria" wrote: The formula I posted returns the average for Jan2006 which is .86245 ..I only find 2 entries for Jan2006....Am I missing something..... -- Jacob (MVP - Excel) "George A. Jululian" wrote: Many thanks the result should read 0.860926 not 0.893833 please advice regards "Jacob Skaria" wrote: Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =AVERAGE(IF(TEXT(A1:A10,"mmmyyyy")="Jan2006",B1:B1 0)) -- Jacob (MVP - Excel) "George A. Jululian" wrote: Hi, Colunm A ColunmB 01/17/2006 0.8631 01/18/2006 0.8618 02/19/2006 0.8625 02/20/2006 0.8613 03/21/2006 0.8582 04/22/2006 0.8661 i need formula to find average rate for colunm b for each month in colunm A regrads "Jacob Skaria" wrote: Hi George Could you please elaborate your query..preferably with an example... -- Jacob (MVP - Excel) "George A. Jululian" wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
That should be taken care..
Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" -- Jacob (MVP - Excel) "George A. Jululian" wrote: Sir if i sort the table and made the only for 31 day the answar 0.860925806 which is tture but the table 365 days and mixed date and month regards "Jacob Skaria" wrote: I am getting 0.860925806....Please check the formula range and try again... -- Jacob (MVP - Excel) "George A. Jululian" wrote: i sort the table 01/01/2006 0.8436 01/02/2006 0.8443 01/03/2006 0.8437 01/04/2006 0.8431 01/05/2006 0.846 01/06/2006 0.8618 01/07/2006 0.8603 01/08/2006 0.8635 01/09/2006 0.8635 01/10/2006 0.8625 01/11/2006 0.8602 01/12/2006 0.8606 01/13/2006 0.8603 01/14/2006 0.8565 01/15/2006 0.8667 01/16/2006 0.8668 01/17/2006 0.8631 01/18/2006 0.8618 01/19/2006 0.8625 01/20/2006 0.8613 01/21/2006 0.8582 01/22/2006 0.8661 01/23/2006 0.8661 01/24/2006 0.8707 01/25/2006 0.8746 01/26/2006 0.8738 01/27/2006 0.872 01/28/2006 0.8682 01/29/2006 0.863 01/30/2006 0.863 01/31/2006 0.8609 Regards "Jacob Skaria" wrote: The formula I posted returns the average for Jan2006 which is .86245 ..I only find 2 entries for Jan2006....Am I missing something..... -- Jacob (MVP - Excel) "George A. Jululian" wrote: Many thanks the result should read 0.860926 not 0.893833 please advice regards "Jacob Skaria" wrote: Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =AVERAGE(IF(TEXT(A1:A10,"mmmyyyy")="Jan2006",B1:B1 0)) -- Jacob (MVP - Excel) "George A. Jululian" wrote: Hi, Colunm A ColunmB 01/17/2006 0.8631 01/18/2006 0.8618 02/19/2006 0.8625 02/20/2006 0.8613 03/21/2006 0.8582 04/22/2006 0.8661 i need formula to find average rate for colunm b for each month in colunm A regrads "Jacob Skaria" wrote: Hi George Could you please elaborate your query..preferably with an example... -- Jacob (MVP - Excel) "George A. Jululian" wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
This is the formula
{=AVERAGE(IF(TEXT(A6:A370,"mmmyyyy")="Jan2006",B6: B370))} "Jacob Skaria" wrote: That should be taken care.. Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" -- Jacob (MVP - Excel) "George A. Jululian" wrote: Sir if i sort the table and made the only for 31 day the answar 0.860925806 which is tture but the table 365 days and mixed date and month regards "Jacob Skaria" wrote: I am getting 0.860925806....Please check the formula range and try again... -- Jacob (MVP - Excel) "George A. Jululian" wrote: i sort the table 01/01/2006 0.8436 01/02/2006 0.8443 01/03/2006 0.8437 01/04/2006 0.8431 01/05/2006 0.846 01/06/2006 0.8618 01/07/2006 0.8603 01/08/2006 0.8635 01/09/2006 0.8635 01/10/2006 0.8625 01/11/2006 0.8602 01/12/2006 0.8606 01/13/2006 0.8603 01/14/2006 0.8565 01/15/2006 0.8667 01/16/2006 0.8668 01/17/2006 0.8631 01/18/2006 0.8618 01/19/2006 0.8625 01/20/2006 0.8613 01/21/2006 0.8582 01/22/2006 0.8661 01/23/2006 0.8661 01/24/2006 0.8707 01/25/2006 0.8746 01/26/2006 0.8738 01/27/2006 0.872 01/28/2006 0.8682 01/29/2006 0.863 01/30/2006 0.863 01/31/2006 0.8609 Regards "Jacob Skaria" wrote: The formula I posted returns the average for Jan2006 which is .86245 ..I only find 2 entries for Jan2006....Am I missing something..... -- Jacob (MVP - Excel) "George A. Jululian" wrote: Many thanks the result should read 0.860926 not 0.893833 please advice regards "Jacob Skaria" wrote: Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =AVERAGE(IF(TEXT(A1:A10,"mmmyyyy")="Jan2006",B1:B1 0)) -- Jacob (MVP - Excel) "George A. Jululian" wrote: Hi, Colunm A ColunmB 01/17/2006 0.8631 01/18/2006 0.8618 02/19/2006 0.8625 02/20/2006 0.8613 03/21/2006 0.8582 04/22/2006 0.8661 i need formula to find average rate for colunm b for each month in colunm A regrads "Jacob Skaria" wrote: Hi George Could you please elaborate your query..preferably with an example... -- Jacob (MVP - Excel) "George A. Jululian" wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
The formula is correct....Try the same formula with few dummy unsorted data
in a small range..... -- Jacob (MVP - Excel) "George A. Jululian" wrote: This is the formula {=AVERAGE(IF(TEXT(A6:A370,"mmmyyyy")="Jan2006",B6: B370))} "Jacob Skaria" wrote: That should be taken care.. Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" -- Jacob (MVP - Excel) "George A. Jululian" wrote: Sir if i sort the table and made the only for 31 day the answar 0.860925806 which is tture but the table 365 days and mixed date and month regards "Jacob Skaria" wrote: I am getting 0.860925806....Please check the formula range and try again... -- Jacob (MVP - Excel) "George A. Jululian" wrote: i sort the table 01/01/2006 0.8436 01/02/2006 0.8443 01/03/2006 0.8437 01/04/2006 0.8431 01/05/2006 0.846 01/06/2006 0.8618 01/07/2006 0.8603 01/08/2006 0.8635 01/09/2006 0.8635 01/10/2006 0.8625 01/11/2006 0.8602 01/12/2006 0.8606 01/13/2006 0.8603 01/14/2006 0.8565 01/15/2006 0.8667 01/16/2006 0.8668 01/17/2006 0.8631 01/18/2006 0.8618 01/19/2006 0.8625 01/20/2006 0.8613 01/21/2006 0.8582 01/22/2006 0.8661 01/23/2006 0.8661 01/24/2006 0.8707 01/25/2006 0.8746 01/26/2006 0.8738 01/27/2006 0.872 01/28/2006 0.8682 01/29/2006 0.863 01/30/2006 0.863 01/31/2006 0.8609 Regards "Jacob Skaria" wrote: The formula I posted returns the average for Jan2006 which is .86245 ..I only find 2 entries for Jan2006....Am I missing something..... -- Jacob (MVP - Excel) "George A. Jululian" wrote: Many thanks the result should read 0.860926 not 0.893833 please advice regards "Jacob Skaria" wrote: Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =AVERAGE(IF(TEXT(A1:A10,"mmmyyyy")="Jan2006",B1:B1 0)) -- Jacob (MVP - Excel) "George A. Jululian" wrote: Hi, Colunm A ColunmB 01/17/2006 0.8631 01/18/2006 0.8618 02/19/2006 0.8625 02/20/2006 0.8613 03/21/2006 0.8582 04/22/2006 0.8661 i need formula to find average rate for colunm b for each month in colunm A regrads "Jacob Skaria" wrote: Hi George Could you please elaborate your query..preferably with an example... -- Jacob (MVP - Excel) "George A. Jululian" wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
no sir no text in A1:A100
"Dave Peterson" wrote: Do you have text in A1:A100? =month() will break if you do. George A. Jululian wrote: FIRST MANY THANKS i tried and adjust the formula (array) it retruned #value! Regards "Pete_UK" wrote: Try this array* formula: =AVERAGE(IF(MONTH(A1:A100)=1,B1:B100)) where the 1 is for January. Adjust the ranges to suit your data. * An array formula must be committed using the key combination of Ctrl- Shift-Enter (CSE) rather than the usual <enter. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. If you need to edit/amend the formula you must use CSE again. If you want to copy the formula down and have it automatically adjust for different months of the year, then you can do this: =AVERAGE(IF(MONTH(A$1:A$100)=ROW(A1),B$1:B$100)) Again, this is an array formula. Hope this helps. Pete On Apr 27, 10:17 am, George A. Jululian wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so . -- Dave Peterson . |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
it worked good but i thanged text fomated date to read day / month / year and
not month and year {=AVERAGE(IF(TEXT(B3:B12,"mmmyyyy")="Jan2006",C3:C 12))} 01/01/2006 2 01/02/2006 3 16/03/2006 4 21/01/2006 4 01/02/2006 6 22/03/2006 8 14/01/2006 6 01/02/2006 9 27/03/2006 12 17/01/2006 8 "Jacob Skaria" wrote: The formula is correct....Try the same formula with few dummy unsorted data in a small range..... -- Jacob (MVP - Excel) "George A. Jululian" wrote: This is the formula {=AVERAGE(IF(TEXT(A6:A370,"mmmyyyy")="Jan2006",B6: B370))} "Jacob Skaria" wrote: That should be taken care.. Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" -- Jacob (MVP - Excel) "George A. Jululian" wrote: Sir if i sort the table and made the only for 31 day the answar 0.860925806 which is tture but the table 365 days and mixed date and month regards "Jacob Skaria" wrote: I am getting 0.860925806....Please check the formula range and try again... -- Jacob (MVP - Excel) "George A. Jululian" wrote: i sort the table 01/01/2006 0.8436 01/02/2006 0.8443 01/03/2006 0.8437 01/04/2006 0.8431 01/05/2006 0.846 01/06/2006 0.8618 01/07/2006 0.8603 01/08/2006 0.8635 01/09/2006 0.8635 01/10/2006 0.8625 01/11/2006 0.8602 01/12/2006 0.8606 01/13/2006 0.8603 01/14/2006 0.8565 01/15/2006 0.8667 01/16/2006 0.8668 01/17/2006 0.8631 01/18/2006 0.8618 01/19/2006 0.8625 01/20/2006 0.8613 01/21/2006 0.8582 01/22/2006 0.8661 01/23/2006 0.8661 01/24/2006 0.8707 01/25/2006 0.8746 01/26/2006 0.8738 01/27/2006 0.872 01/28/2006 0.8682 01/29/2006 0.863 01/30/2006 0.863 01/31/2006 0.8609 Regards "Jacob Skaria" wrote: The formula I posted returns the average for Jan2006 which is .86245 ..I only find 2 entries for Jan2006....Am I missing something..... -- Jacob (MVP - Excel) "George A. Jululian" wrote: Many thanks the result should read 0.860926 not 0.893833 please advice regards "Jacob Skaria" wrote: Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =AVERAGE(IF(TEXT(A1:A10,"mmmyyyy")="Jan2006",B1:B1 0)) -- Jacob (MVP - Excel) "George A. Jululian" wrote: Hi, Colunm A ColunmB 01/17/2006 0.8631 01/18/2006 0.8618 02/19/2006 0.8625 02/20/2006 0.8613 03/21/2006 0.8582 04/22/2006 0.8661 i need formula to find average rate for colunm b for each month in colunm A regrads "Jacob Skaria" wrote: Hi George Could you please elaborate your query..preferably with an example... -- Jacob (MVP - Excel) "George A. Jululian" wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
Do you have or did you have formulas that evaluated to ="" (even if they were
converted to values)? Do you have any non-dates in A1:A100? Remember to look in hidden rows (filtered or manually hidden). George A. Jululian wrote: no sir no text in A1:A100 "Dave Peterson" wrote: Do you have text in A1:A100? =month() will break if you do. George A. Jululian wrote: FIRST MANY THANKS i tried and adjust the formula (array) it retruned #value! Regards "Pete_UK" wrote: Try this array* formula: =AVERAGE(IF(MONTH(A1:A100)=1,B1:B100)) where the 1 is for January. Adjust the ranges to suit your data. * An array formula must be committed using the key combination of Ctrl- Shift-Enter (CSE) rather than the usual <enter. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. If you need to edit/amend the formula you must use CSE again. If you want to copy the formula down and have it automatically adjust for different months of the year, then you can do this: =AVERAGE(IF(MONTH(A$1:A$100)=ROW(A1),B$1:B$100)) Again, this is an array formula. Hope this helps. Pete On Apr 27, 10:17 am, George A. Jululian wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so . -- Dave Peterson . -- Dave Peterson |
|
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Jululian
Many Thanks on your help
"Dave Peterson" wrote: Do you have or did you have formulas that evaluated to ="" (even if they were converted to values)? Do you have any non-dates in A1:A100? Remember to look in hidden rows (filtered or manually hidden). George A. Jululian wrote: no sir no text in A1:A100 "Dave Peterson" wrote: Do you have text in A1:A100? =month() will break if you do. George A. Jululian wrote: FIRST MANY THANKS i tried and adjust the formula (array) it retruned #value! Regards "Pete_UK" wrote: Try this array* formula: =AVERAGE(IF(MONTH(A1:A100)=1,B1:B100)) where the 1 is for January. Adjust the ranges to suit your data. * An array formula must be committed using the key combination of Ctrl- Shift-Enter (CSE) rather than the usual <enter. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. If you need to edit/amend the formula you must use CSE again. If you want to copy the formula down and have it automatically adjust for different months of the year, then you can do this: =AVERAGE(IF(MONTH(A$1:A$100)=ROW(A1),B$1:B$100)) Again, this is an array formula. Hope this helps. Pete On Apr 27, 10:17 am, George A. Jululian wrote: hi all i need your help i have a table with two column first one date second rate for 365 days A B 07/03/2006 0.913 i need average rate for each month if its January the average rate is so . -- Dave Peterson . -- Dave Peterson . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Jululian | Excel Discussion (Misc queries) | |||
| Jululian Excel | Excel Discussion (Misc queries) | |||
| Jululian & Excel | Excel Discussion (Misc queries) | |||
| Excel Jululian | Excel Discussion (Misc queries) | |||
| Excel Jululian | Excel Discussion (Misc queries) | |||
![George A. Jululian[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
