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.