Getting the average of the contents of a range of cells based on a condition

  • Thread starter Thread starter mahoobley
  • Start date Start date
M

mahoobley

Err, right. Will try to make this as easy to explain as possible.

Was generously given an equation to use that calculated how many cell
contain a particular bit of text based on a date range determined in
differenct column on the same row. This bit of code was as follows:

=SUMPRODUCT(--(MONTH('Raw data'!$F$1:$F$10000)=12),--(YEAR('Ra
data'!$F$1:$F$10000)=2003),--('Raw data'!$L$1:$L$10000="Monkeys"))

Which basically looks at a date in column F and sees if the date i
there is within the 12th month of the year 2003 (ie in December 2003)
then looks in column L within rows that fit that previous dat
criteria, then gives a number based on how many of those cells contai
the word 'Monkeys'.

Now, I need to adapt that code to do something different.

I still need pull data from a range of cells based on a date criteria
but this time rather than looking for the amount of instances of cell
containing a word, I need to get an average of a number that is in th
filtered cells.


Code
-------------------


|---- A ----|---- B ----|
| Date | Monkeys |
| 13-Dec-03 | 12 |
| 20-Dec-03 | 20 |
| 29-Dec-03 | - |
| 31-Dec-03 | 11 |
| 12-Jan-03 | 2 |
| 22-Jan-03 | 7 |


-------------------


So, how could I work out how many monkeys there were on average fo
each day measured in December, bearing in mind some of these cells ma
have no value in them and I don't want these empty cells to mess up th
average.

Thanks!

(Please Note - I have to regretably inform you that the work I a
currently performing has nothing to do with monkeys. Its just a
analogy. Sorry.
 
Hi
one way:
=SUMPRODUCT(--(MONTH(A1:A100)=12),--(YEAR(A1:A100)=2003),B1:B100)/SUMPR
ODUCT(--(MONTH(A1:A100)=12),--(YEAR(A1:A100)=2003))

or try the following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF((A1:A100>=DATE(2003,12,1))*(A1:A100<=DATE(2003,12,31)),B1:B
100))
 
Back
Top