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

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

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

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

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

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

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

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

.

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

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
.

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