Exclude Array or Range from Averageifs formula

[witherer]

My "Data" worksheet includes Name (column A), Problem Reported Date (column B), Problem Resolved Date (column C), Days to Resolve (column D), Type of Problem (column E). My "Analysis" worksheet includes Names (column A), Date Person Started In Department (column B), Date Person Left Department (column C), Type of Problem (column D).

The Data worksheet contains many entries from multiple departements of people who resolved work problems (called Discrepancy Logs (DLs)) over a large span of time. I want to calculate average DL response time, but exclude the DLs resolved from the people in my department during the timeframe they were here, which is the data in the "Analysis" worksheet.

My formula in the Analysis worksheet averages the response time of a "DL" from the data worksheet, and attempts to exclude the peoples names in my department (A2:A7).

=IFERROR(AVERAGEIFS(Data!$D$2:$D$45,Data!$E$2:$E$45,"="&"DL",Data!$A$2:$A$45,"<>"&A2,Data!$A$2:$A$45,"<>"&A3,Data!$A$2:$A$45,"<>"&A4,Data!$A$2:$A$45,"<>"&A5,Data!$A$2:$A$45,"<>"&A6,Data!$A$2:$A$45,"<>"&A7,Data!$A$2:$A$45,"<>"&A8),"")

However, they went on to resolve problems in other departments and I only want to exclude them, and the DLs they resolved, for the time they were working in my department. Somehow I to incorporate the data within the date range they were here (date range of column B and C).

I believe I'm trying to exclude an array of data from the averageifs formula, but I don't have any experience with using arrays.

Thanks in advance.

 

[Aladin Akyurek]

Unclear what do you want to include exactly... Given:

D2:D45 = Range to average
E2:E45 = DL
A2:A45 = people
A2:A7 = target people
B2:B45 >= X
C2:C45 <= X

where X is a date of interest.

Care to elaborate?

 

[witherer]

Thank you, I'll be more specific.
Data Worksheet:
Data!$D$2:$D$45 = Range to average. Number of days for that person to answer that DL or DR.
Data!$E$2:$E$45 = Range of document type DL (Discrepancy Log) or DR (Discrepancy Report). I want only DL's answered.
Data!$A$2:$A$45 = People that answered the DL or DR.
Data!$B$2:$B$45 = Date the DL or DR was started. Only used to generate Data!$D$2:$D$45 number.
Data!$C$2:$C$45 = Date the DL or DR was completed. Ex. C2-B2 = 3 days (this creates range Data!$D$2:$D$45)

My formula is in another worksheet called "Analysis", so I can import a fresh set of data every month to the Data worksheet and not mess up any formulas.
A2:A8 = Target people I want to exclude from the Average days to answer a DL.
B2:B8 = Date the each target person started working in the department.
C2:C8 = Date each target person left the department. Note: They (and others) keep answering DLs in other departments, which is what I want to average.

Goal: I want to lookup people in Data!$A$2:$A$45 for matching people listed in A2:A8, and exclude the corresponding entry in the range to average (Data!$D$2:$D$45), if "DL" appears in (Data!$E$2:$E$45), and if the date in (Data!$B$2:$B$45) is between the dates that person was in the department (B2:B8 to C2:C8).

Thanks for the help.