Monthy Averages

  • Thread starter Thread starter Bergerac
  • Start date Start date
B

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
 
Hi
could you please provide some more details about how your
spreadsheet is layouted and how your data is strcutred
 
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
 
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
 
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!.
 
Back
Top