Excel Jululian

G

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
 
J

Jacob Skaria

Hi George

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

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
 
G

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
 
J

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

George A. Jululian

FIRST MANY THANKS

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

Regards
 
G

George A. Jululian

Many thanks

the result should read 0.860926 not 0.893833

please advice

regards
 
J

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

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
 
G

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
 
J

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

George A. Jululian

This is the formula

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

Jacob Skaria

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

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
 
D

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

George A. Jululian

Many Thanks on your help

Dave Peterson said:
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).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top