Excel Jululian

[George A. 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

 

[Jacob Skaria]

Hi George

Could you please elaborate your query..preferably with an example...

 

[Pete_UK]

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

 

[George A. 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]

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:B10))

 

[George A. Jululian]

FIRST MANY THANKS

i tried and adjust the formula (array)
it retruned #value!

Regards

 

[George A. Jululian]

Many thanks

the result should read 0.860926 not 0.893833

please advice

regards

 

[Jacob Skaria]

The formula I posted returns the average for Jan2006 which is .86245 ..I only
find 2 entries for Jan2006....Am I missing something.....

 

[George A. 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]

I am getting 0.860925806....Please check the formula range and try again...

 

[Dave Peterson]

Do you have text in A1:A100?

=month() will break if you do.

 

[George A. 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]

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>}"

 

[George A. Jululian]

This is the formula

{=AVERAGE(IF(TEXT(A6:A370,"mmmyyyy")="Jan2006",B6:B370))}

 

[Jacob Skaria]

The formula is correct....Try the same formula with few dummy unsorted data
in a small range.....

 

[George A. Jululian]

no sir no text in A1:A100

Do you have text in A1:A100?

=month() will break if you do.

 

[George A. 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:C12))}

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

 

[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]

Many Thanks on your help

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).