Monthy Averages

[Bergerac]

Hello,

I have a spreadsheet where I enter a date and information
comes up on what happened on that date, however I need a
formulae that calculates the monthy average from 5
different accounts of said date and returns it to me, I
have no idea how to do this can anyone help?

Thanx

 

[Frank Kabel]

Hi
could you please provide some more details about how your
spreadsheet is layouted and how your data is strcutred

 

[Guest]

The Data is laid out in a format like this:

11-Mar-04
12-Mar-04 1721
13-Mar-04
14-Mar-04
15-Mar-04
16-Mar-04 3581
17-Mar-04 5027
18-Mar-04 6243
19-Mar-04
20-Mar-04
21-Mar-04
22-Mar-04
23-Mar-04
24-Mar-04
25-Mar-04
26-Mar-04
27-Mar-04
28-Mar-04
29-Mar-04
30-Mar-04
31-Mar-04
01-Apr-04
02-Apr-04 6382
03-Apr-04 2297
04-Apr-04 5214
05-Apr-04 3311
06-Apr-04 1648
07-Apr-04
08-Apr-04






And the spreadsheet like this:


Enter Date: 04-Apr-04




Online Monthly Potential x


in the cell where the x is situated I would like the
average of all the data for just that month i.e only
using the data above a day in April would = 3770.4, and a
day in March would = 3314.4

 

[Frank Kabel]

Hi
try the array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF((MONTH(A2)=MONTH('sheet1'!$A$1:$A$100))*(YEAR
(A2)=YEAR('sheet1'!$A$1:$A$100)),'sheet1'$B$1:$F$100))

Assumption:
sheet 1 stores your data
cell A2 is the date on your second sheet

 

[Domenic]

Hi,

Try the following...

=AVERAGE(IF((MONTH(A1:A29)=MONTH(D1))*(YEAR(A1:A29)=YEAR(D1))*(B1:B29<>""
),B1:B29))

entered using CONTROL+SHIFT+ENTER, and where D1 contains the date of
interest.

Hope this helps!.