Average with 2 criteria including month
Dear Peo,
I don't have text values for the dates, they are entered in the m/d/yy
format. As JE McGimpsey noted, having a #VALUE! w/in the column would give
me this error. As I told him, the F1500 was just a typo in my post not in
the actual formula. Thanks for noting it though. If you read my latest
posts, the issue isn't quite solved yet.
"Peo Sjoblom" wrote:
If you have text values for the months like January as opposed to for
instance Excel dates like
01/15/07 then you will get a value error just by using the month function
Also the formula you posted has a typo, it should be (I presume)
=AVERAGE(IF((J17:J1500="ABC")*(MONTH(I17:I1500)=1) ,F17:F1500))
and it needs to be entered with ctrl + shift & enter,
--
Regards,
Peo Sjoblom
"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.
|