For one thing

MONTH(I17:I1500)=1

will include any cells that are blanks since blank cells are equal to zero
and Jan zero 1900 is when Excel starts recognizing dates, so another
alternative would be

=AVERAGE(IF((J17:J1500="ABC")*(I17:I1500=--"2007-01-01")*(I17:I1500<=--"2007-01-31"),F17:F1500))

array entered

also if you want to make sure the count is correct vis-à-vis

=SUMPRODUCT(--(J17:J1500="ABC"),--(I17:I1500=--"2007-01-01"),--(I17:I1500<=--"2007-01-31"))

should return 25 using your criteria

of course the hardcoded dates for Jan 01 should be replaced with cell where
you type in the dates, and so should "ABC" be, that way you don't need to
edit the formula when you change dates and criteria etc



--
Regards,

Peo Sjoblom



RS" wrote in message
...
Dear JE McGimpsey,

Thanks for your response. You're right. There were actually 2 items
(#VALUE! and #NUM!) which were causing my formula to not work. The F1500
omission was actually just a typo in my post, but thanks for pointing it
out
to me.

However, the formula is still not displaying the correct average. The
value
it is currently showing is greater than the value I calculate when using
the
autofilters for the case type (ABC) and closing date (col J = 1/1/07 And
<2/1/07. When I do this, there are 25 cases displayed via
=SUBTOTAL(3,J17:J1500) and the average of this filtered subtotal is 94 via
=SUBTOTAL(1,F17:F1500). This value is the correct one that I should be
getting w/ our formula.

I noticed that my client had deleted one of the formulas in col F (#
days).
Interestingly, when I copied the formula from the previous cell, the value
in
the cell containing the average formula changed even though this row
didn't
have a close date entered. Just to inform you, not all rows have close
dates, though col F (# days) can contain a valid # because I have an
additional column which has projected close dates (col H). This shouldn't
affect the calculation because this date is in a separate column. Also, 3
of
the rows have a "-" in them because I did a custom format whereby if the
value is blank, it displays "-". However, none of these 3 have close
dates
of January in column F.

I did a little test whereby I autofiltered for ABC and then deleted all
these ABC values from col J except 1 (in Row 860) that had a blank close
date
(col I) but a projected close date (col H) of 6/30/07 giving a value of
111
in col F. Sure enough, the average formula displayed 111 even though
there
is no close date in the month of January for this row. What's wrong w/
the
formula?

"JE McGimpsey" wrote:

Works OK for me (once I changed F17:1500 to F17:F1500).

Something that can cause the #VALUE! error is having a #VALUE! error
somewhere in your argument ranges that is then passed through. Have you
checked that?

In article ,
RS wrote:

In Excel 2000, I'm trying to create a formula whereby it finds case
types
(ex: ABC) that close in a particular month (ex. January) and calculates
the
average length those cases were open. What's wrong w/ my formula? I
get the
#VALUE! displayed. I've also tried entering it as an array formula w/
the
same result.

=AVERAGE(IF((J17:J1500="ABC")*(MONTH(I17:I1500)=1) ,F17:1500)).

Column J contains the case types, column I has the close dates (ex.
1/15/07), and col F calculates the # of days a case is open based on
the open
date (col G) & close date (col I).

Now I know that I can use Autofilters for the the case types & close
dates
and the =SUBTOTAL(1,F17:F1500) formula, but I want to have the values
for
each of these months readily available.