Average with 2 criteria including month

[RS]

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.

 

[Bernie Deitrick]

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

 

[JE McGimpsey]

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?

 

[Peo Sjoblom]

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,

 

[RS]

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?

 

[RS]

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?

 

[RS]

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]

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

 

[RS]

Dear Peo,

Thanks for your suggestion, I had no idea Excel would treat a blank cell as
Jan 1900. Seems kind of silly since there's no data in the cell. I could
understand if someone entered in Jan 1900. Anyway, using your formula gives
me the correct average of 94. I already had a formula that was displaying
the correct count and this formula also made use of references to cells
containing the service type and date (much as you suggested). I simply left
out these references in my post to make it easier to understand. The formula
I was using to double-check the count (similar to yours) is:

=SUMPRODUCT(--(TEXT($I$17:$I$1500,"mmmyyyy")=TEXT(AD$2,"mmmyyyy")),--($J$17:$J$1500=$AQ12))

where AD$2 refers to a date formatted cell and $AQ12 = ABC.

Modifying your formula to include the references I used in my SUMPRODUCT
equation gives the following array-entered formula for calculating averages
correctly:

=AVERAGE(IF(($J$17:$J$1500=$AQ12)*(TEXT($I$17:$I$1500,"mmmyyyy")=TEXT(AD$2,"mmmyyyy")),F17:F1500))

Using this formula, I can simply copy this formula across a grid & make a
table for the different service types for each month. Thanks so much for
your assistance!