I am getting the #DIV/0 error.
=AVERAGE(IF(Work!$Q$3:$Q$4293=DATE(2008,11,1),IF (Work!$R$3:$R$4293<"",IF(Work!$Q$3:$Q$4293<DATE(2 008,12,1),Work!$C$3:$C$4293,""))))

There's nothing wrong with that formula.

I know there should be data

Then there's a problem with your data. Are you sure your dates are true
Excel dates? What result do you get with this formula:

=SUMPRODUCT(--(MONTH(Work!Q3:Q4293)=11),--(YEAR(Work!Q3:Q4293)=2008))

If you get 0 that means that either your dates are not true Excel dates or
there are no dates for Nov 2008.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
Not quite right. I am getting the #DIV/0 error. I know there should be
data
based on the original formula I used. Which is below. I have some many
tables with multiple rows it was too cumbersone. I hope to be able to
figure
out what is wrong with the formula you and T. Valko provided.


=AVERAGE(IF(Work!$Q$3:$Q$4293=DATE(2008,11,1),IF( Work!$R$3:$R$4293<"",IF(Work!$Q$3:$Q$4293<DATE(20 08,12,1),Work!$C$3:$C$4293,""))))



"Mike H" wrote:

Hi,

The average formula, ARRAY entered

=AVERAGE(IF(TEXT(Works!Q3:Q4293,"mmyyyy")=TEXT(A6, "mmyyyy"),IF(Works!R3:R4293<"",Works!C3:C4293 )))

and the COUNT formula., once again ARRAY entered

=COUNT(IF(TEXT(Works!Q3:Q4293,"mmyyyy")=TEXT(A6,"m myyyy"),IF(Works!R3:R4293<"",Works!C3:C4293)))

Mike



"PAL" wrote:

I am trying to get the average of a set of numbers if they occur in a
given
month. I get the infamous #N/A. As an array.......

=AVERAGE(IF(TEXT(Work!$Q$3:$Q$4293,"mmyyyy")=TEXT( A6,"mmyyyy")),IF(Work!$R$3:$R$4293<"",Work!$C$2:$ C$4293)))

I would also like to count the number. I would assume I could swap out
average for count.