how do I perform a conditional average with exclusions?

[Sandre]

I am trying to perform a conditional average with the following conditions:

ColumB is the document type, (i.e., change of address). Column E is the
source of the information (i.e., Internal, mail, etc). Column H is the
network day turn around (formula NETWORKDAYS).

Document_Type Source TAT
Address Change T 4
Address Change C 10
Address Change I 1


I am trying to get an average of all TATs for address changes that do NOT
include source codes C or I.

I am using Excel 2000, and I can get conditional sum to work with those
parameters, and I can get a basic "average if" function to work, but the
minute I try to exclude C and I, I get a value of 0, which is incorrect.

Anyone have any suggestions on a formula?

Thanks!

 

[Luke M]

=SUM((B2:B100="Address
Change")*(E2:E100<>"C")*(E2:E100<>"I")*(H2:H100))/COUNT((B2:B100="Address
Change")*(E2:E100<>"C")*(E2:E100<>"I"))

Adjust range sizes as needed. Input formula as an array (Ctrl+Shift+Enter)

 

[Sandre]

Wow, that was a great start. I never thought of manually performing the calc
instead of an average if. Thank you!!

The only problem I ran into is that not all of column B is address changes.
I got a value error when I used this formula, so I am thinking that it's
maybe because there are other things in the same column (that will also need
to be averaged).

I have bank changes, cancellations, and other categories of transactions, so
I know there has to be an "If" in there somewhere.

Thank you for all the help!!

 

[Mike H]

Maybe this

=AVERAGE(IF(B2:B100="Address
Change",IF(E2:E100<>"T",IF(E2:E100<>"I",H2:H100))))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

 

[Luke M]

Hmm, the first part of formula is checking that, it should only be including
the "Address changes". Make sure that the arrays are all the same length,
(and not entire column references, e.g. E:E). Also, it must be entered as an
array (Ctrl+Shift+Enter) so that the {} brackets appear around formula.

Mike H's formula appears shorter, I wasn't sure if AVERAGE included the
FALSE values in its calculation. From a time/calculation saving standpoint,
I'd recommed his formula then.

 

[Sandre]

That's it, thank you!!!

Here's the final formula with ALL of the parameters factored in:

=IF(C11=0,0,AVERAGE(IF('\\XXXXXX\[TAT
Report.xls]Sheet1'!$B$1:$B$20000="Address
Change",IF('\\Ias-fs\pos\Public\KSKJ_Life\Reports\[TAT
Report.xls]Sheet1'!$E$1:$E$20000<>"C",IF('\\XXXXXX\[TAT
Report.xls]Sheet1'!$E$1:$E$20000<>"I",'\\XXXXXX\[TAT
Report.xls]Sheet1'!$H$1:$H$20000)))))

Thank you both, you are awesome