RS,

One way:

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

Array entered with Ctrl-Shift-Enter

HTH,
Bernie
MS Excel MVP


"RS" wrote in message
...
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.

Dear Bernie,

Thanks for your response. You're formula also worked once I removed the
#VALUE! and #NUM! from the spreadsheet. Interestingly, even though both
values were present, your formula displayed the #NUM! value even though the
#VALUE! appeared earlier on in the spreadsheet. Removing the #NUM! error
allowed the formula to perform the calculation while ignoring the #VALUE!.
However, as I noted in my post to JE McGimpsey, the formula is still not
correctly calculating the average. Do you know what's wrong?

"Bernie Deitrick" wrote:

RS,

One way:

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

Array entered with Ctrl-Shift-Enter

HTH,
Bernie
MS Excel MVP


"RS" wrote in message
...
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.